How To auto clear contents in a column and make the new values that will be added to accumulate a certain column

[Nothando]

Please help with the VB code for the following issue i need to sort.
i have an excel spread sheet and i want column " I " to auto delete contents everyday,
then i want the new values that i will add on column I to accumulate or be added on the Previous numbers that are in column G.
Column G contains the total numbers of what has been done from day 1 January to 31 December every year, so column I is what am doing daily.
so i want my daily numbers to auto delete and the new values added to accumulate in column G.
Kindly Help with the right VB code to do this.
Thank you....

 

[maabadi]

1. Go to Cell G81 & Delete space on it.
2. Then Test this code:

Private Sub Workbook_Open()
Dim i As Long, Lr As Long, Sh As Worksheet
Set Sh = Sheets("Raw Data")
With Sh
Lr = .Range("I" & Rows.Count).End(xlUp).Row
For i = 2 To Lr
if Range("G" & i).Value = "" Or Range("G" & i).Value = " " Then Range("G" & i).Value = 0
if Range("I" & i).Value = "" Or Range("I" & i).Value = " " Then Range("I" & i).Value = 0
.Range("G" & i).Value = .Range("I" & i).Value + .Range("G" & i).Value * 1
Next i
.Range("I2:I" & Lr).ClearContents
End With
End Sub

 

[Nothando]

1. Go to Cell G81 & Delete space on it.
2. Then Test this code:

Private Sub Workbook_Open()
Dim i As Long, Lr As Long, Sh As Worksheet
Set Sh = Sheets("Raw Data")
With Sh
Lr = .Range("I" & Rows.Count).End(xlUp).Row
For i = 2 To Lr
if Range("G" & i).Value = "" Or Range("G" & i).Value = " " Then Range("G" & i).Value = 0
if Range("I" & i).Value = "" Or Range("I" & i).Value = " " Then Range("I" & i).Value = 0
.Range("G" & i).Value = .Range("I" & i).Value + .Range("G" & i).Value * 1
Next i
.Range("I2:I" & Lr).ClearContents
End With
End Sub

i have tested the above code...ok when i open the workbook,the values in I are deleted but when im adding a new value in I, the value in G is not accumulated.

 

[maabadi]

The values only added when open workbook. This is very easy & faster way.
We can do also with worksheet change event but whenever you inout data at column I then Code runs & causes your files slow.
But if want I can change code to worksheet change event.

 

[Nothando]

i have tested the above code...ok when i open the workbook,the values in I are deleted but when im adding a new value in I, the value in G is not accumulated.

ok thank you so much Maabadi...ive just closed and opened the workbook again, the code is working...the values in G were accumulated... but can you please make the code to accumulate the value the moment you add it on I...such that it doesn't wait for you to close and open the workbook before you see the accumulations

 

[Nothando]

ok thank you so much Maabadi...ive just closed and opened the workbook again, the code is working...the values in G were accumulated... but can you please make the code to accumulate the value the moment you add it on I...such that it doesn't wait for you to close and open the workbook before you see the accumulations

lets say maybe there is 2 in column G..and am typing 2 in column I..i want to see column G changing to 4 instantly, i dont want to see the 4 on the following day when am opening the workbook again

 

[maabadi]

1. First Delete Workbook Open Code
2. At Excel window, Right Click on Sheet Name and Select View Code
3. Paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, Lr As Long
Lr = Range("I" & Rows.Count).End(xlUp).Row
If Intersect(Target, Range("I2:I" & Lr)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
i = Target.Row
If Range("G" & i).Value = "" Or Range("G" & i).Value = " " Then Range("G" & i).Value = 0
If Range("I" & i).Value = "" Or Range("I" & i).Value = " " Then Range("I" & i).Value = 0
Range("G" & i).Value = Range("I" & i).Value + Range("G" & i).Value * 1
Range("I" & i).ClearContents
Application.EnableEvents = True
End Sub

 

[Nothando]

pl

The values only added when open workbook. This is very easy & faster way.
We can do also with worksheet change event but whenever you inout data at column I then Code runs & causes your files slow.
But if want I can change code to worksheet change event.

Please can we do the change event code

 

[Nothando]

n

1. First Delete Workbook Open Code
2. At Excel window, Right Click on Sheet Name and Select View Code
3. Paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, Lr As Long
Lr = Range("I" & Rows.Count).End(xlUp).Row
If Intersect(Target, Range("I2:I" & Lr)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
i = Target.Row
If Range("G" & i).Value = "" Or Range("G" & i).Value = " " Then Range("G" & i).Value = 0
If Range("I" & i).Value = "" Or Range("I" & i).Value = " " Then Range("I" & i).Value = 0
Range("G" & i).Value = Range("I" & i).Value + Range("G" & i).Value * 1
Range("I" & i).ClearContents
Application.EnableEvents = True
End Sub

Nothing is happening ..when i paste this code, it doesnt refresh column I...and values are not accumulated in column G

 

[maabadi]

Values only accumulated after input data at column I.
Are you right click on sheet name & then add codes