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]

Try this macro after inputing all data at column I every day.

Sub ADDValues()
Dim i As Long, Lr As Long
Lr = Range("I" & Rows.Count).End(xlUp).Row
For i = 1 To Lr
Range("G" & i).Value = Range("I" & i).Value + Range("G" & i).Value
Next i
Range("I1:I" & Lr).ClearContents
End Sub

 

[Nothando]

Try this macro after inputing all data at column I every day.

Sub ADDValues()
Dim i As Long, Lr As Long
Lr = Range("I" & Rows.Count).End(xlUp).Row
For i = 1 To Lr
Range("G" & i).Value = Range("I" & i).Value + Range("G" & i).Value
Next i
Range("I1:I" & Lr).ClearContents
End Sub

thanks Maabadi, however does this mean everyday i have to add this code to my worksheet after adding the new values in column I? , cos i need a code that will auto delete everyday...such that when i open the spreadsheet the next day, i find column I empty or without today's data, then i add the new data/numbers and those numbers i add accumulate the numbers in column G.
please see attached snapshot, to get sense of how it looks like..
thanks.

 

[Nothando]

Try this macro after inputing all data at column I every day.

Sub ADDValues()
Dim i As Long, Lr As Long
Lr = Range("I" & Rows.Count).End(xlUp).Row
For i = 1 To Lr
Range("G" & i).Value = Range("I" & i).Value + Range("G" & i).Value
Next i
Range("I1:I" & Lr).ClearContents
End Sub

I copied the above code and it is giving me an error.
please see attached image

 

[maabadi]

No need to Add this code every day. only input one time then save file as Macro-enabled workbook (.xlsm)
Then after finish input Data at column I , Go to View Tab, View Macros (Or Press ALT Key them W , M , V )and Select ADDValues Macro And Press Run.

For Error your Data start from row 2 then Change At this line 1 to 2

For i=1 to Lr

to

For i=2 to Lr

 

[maabadi]

if you want Workbook open event, I can write it also then
1. Are you want also Delete column I data when Open file
2. Or First add data at column I to G then Delete Column I data when Open files?
For Second Situation. Right Click on Thisworkbook name at VBA window and Select view Code, then Paste this code
Also change sheet name to your sheet name.

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
.Range("G" & i).Value = .Range("I" & i).Value + .Range("G" & i).Value
Next i
.Range("I1:I" & Lr).ClearContents
End With
End Sub

 

[Nothando]

ok

if you want Workbook open event, I can write it also then
1. Are you want also Delete column I data when Open file
2. Or First add data at column I to G then Delete Column I data when Open files?
For Second Situation. Right Click on Thisworkbook name at VBA window and Select view Code, then Paste this code
Also change sheet name to your sheet name.

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
.Range("G" & i).Value = .Range("I" & i).Value + .Range("G" & i).Value
Next i
.Range("I1:I" & Lr).ClearContents
End With
End Sub

i want the second option, firstly i add data on column I...then that data accumulate numbers in column G.....i dont mind if that data i add in colum I can stay there for the whole day..as long as it will auto refresh the next day for me to add new values, that will accumulate column G again...

 

[maabadi]

Then Use my code uploaded at post #6
It first accumulate numbers from column I to G then Delete data at column I Whenever you open file.

 

[Nothando]

if you want Workbook open event, I can write it also then
1. Are you want also Delete column I data when Open file
2. Or First add data at column I to G then Delete Column I data when Open files?
For Second Situation. Right Click on Thisworkbook name at VBA window and Select view Code, then Paste this code
Also change sheet name to your sheet name.

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
.Range("G" & i).Value = .Range("I" & i).Value + .Range("G" & i).Value
Next i
.Range("I1:I" & Lr).ClearContents
End With
End Sub

it is now giving me a new error(attached), i have changed sheet name

 

[maabadi]

Not Sheet1(Raw Data) only sheet name within Parenthesis then Don't change my code & use it.