Adding "A1,A2,A3.." to "B1,B2,B3.." Then Row "A" resets value to Zero

Question

I am currently trying to add a script into excel. excuse my terminology, I am not that hot with programming!

I do all of my accounting on excel 2003, and I would like to be able to add the value of say cells f6 to f27 to the cells e6 to e27, respectively. The thing is, I want the value of the "f" column to reset every time.

So far I have found this code, which works if I copy and paste it into VBA. but it only allows me to use it on one row:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = Range("f7").Address Then
        Range("e7") = Range("e7") + Range("f7")
        Range("f7").ClearContents
    End If
    Application.EnableEvents = True
End Sub

would somebody be kind enough to explain how I can edit this to do the same through all of my desired cells? I have tried adding Range("f7",[f8],[f9] etc.. but i am really beyond my knowledge.

Answer 1

First, you need to define the range which is supposed to be "caught"; that is, define the range you want to track for changes. I found an example here. Then, simply add the values to the other cell:

Private Sub Worksheet_Change(ByVal Target as Range)
    Dim r as Range ' The range you'll track for changes
    Set r = Range("F2:F27")

    ' If the changed cell is not in the tracked range, then exit the procedure
    ' (in other words, if the intersection between target and r is empty)
    If Intersect(Target, r) Is Nothing Then
        Exit Sub
    Else
        ' Now, if the changed cell is in the range, then update the required value:
        Cells(Target.Row, 5).Value = Cells(Target.Row, 5).Value + Target.Value
        ' ----------------^
        ' Column 5 = 
        '       column "E"

        ' Clear the changed cell
        Target.ClearContents
    End if
End Sub

Hope this helps

Answer 2

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Intersect(Target, Range("B1:B5,F6:F27")) Then 'U can define any other range   
        Target.Offset(0, -1) = Target.Offset(0, -1).Value + Target.Value ' Target.Offset(0,-1) refer to cell one column before the changed cell column.
        'OR: Cells(Target.row, 5) = Cells(Target.row, 5).Value + Target.Value '  Where the 5 refer to column E
        Target.ClearContents
    End If

ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub