Running Total Using VBA Macros

[treohr]

Hey all,

This is my first ever attempt at using macros on Excel, so forgive me if this is a simple question.

I am currently trying to write a code for a running total between two cells. Online I was able to find the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("F3"), Target) Is Nothing Then ExitSub
[G3] = [F3] + [G3]
End Sub

At first I tried copying and pasting this code several times and simply changing the Cells referenced, but after some further reading, if I understand correctly, there can be only one "Worksheet_Change" Sub. So I changed my code to the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Range("F3"), Target) Then
    [G3] = [F3] + [G3]
    End If
    If Intersect(Range("F4"), Target) Then
    [G4] = [F4] + [G4]
    End If
    If Intersect(Range("F5"), Target) Then
    [G5] = [F5] + [G5]
    End If
    If Intersect(Range("F6"), Target) Then
    [G6] = [F6] + [G6]
    End If
    If Intersect(Range("F7"), Target) Then
    [G7] = [F7] + [G7]
    End If
    If Intersect(Range("F8"), Target) Then
    [G8] = [F8] + [G8]
    End If
    If Intersect(Range("F9"), Target) Then
    [G9] = [F9] + [G9]
    End If
End Sub

However, I now get an error:

"Run-time error '91':
Object variable or With block variable not set"

After adding "Is Object" to my code in all of the If-Then statements:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Range("F3"), Target) Is Object Then
    [G3] = [F3] + [G3]
    End If
    If Intersect(Range("F4"), Target) Is Object Then
    [G4] = [F4] + [G4]
    End If
    If Intersect(Range("F5"), Target) Is Object Then
    [G5] = [F5] + [G5]
    End If
    If Intersect(Range("F6"), Target) Is Object Then
    [G6] = [F6] + [G6]
    End If
    If Intersect(Range("F7"), Target) Is Object Then
    [G7] = [F7] + [G7]
    End If
    If Intersect(Range("F8"), Target) Is Object Then
    [G8] = [F8] + [G8]
    End If
    If Intersect(Range("F9"), Target) Is Object Then
    [G9] = [F9] + [G9]
    End If
End Sub

And got the error:

"Run-time error '424':
Object required'

I understand enough about coding, in general, to be relatively certain that there is some error with definitions and/or data type, but I don't know excel well enough to fix this.


As an example of what I want my code to do, I am trying to track weekly hours and total hours spent doing a task. For example, in F3, I would like to add the hours spent doing a task that week, and then have the cell G3 update (as a running total) with the previous value in G3 added to the new value entered into F3. I would like to do this for 7 different cases, each one updating separately. The closest I got was this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Range("F3:F9"), Target) Is Nothing Then Exit Sub
    [G3] = [F3] + [G3]
    [G4] = [F4] + [G4]
    [G5] = [F5] + [G5]
    [G6] = [F6] + [G6]
    [G7] = [F7] + [G7]
    [G8] = [F8] + [G8]
    [G9] = [F9] + [G9]
End Sub

Which did what I wanted, but if I updated any of the Cells in F3:F9, it would re-add the others. So like if I had 10 hours for the week in F3, G3 would go to 10, but if I then added 5 hours in F4, G3 would go to 20 (adding F3 again) and G4 would go to 5, and so on.


Again I apologize if this is an easy fix, but any help would be appreciated.

Thanks!

- Tyler

[JohnTopley]

try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Range("F3:F9"), Target) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target.Offset(0, 1) = Target.Offset(0, 1) + Target.Value
    Application.EnableEvents = True
End Sub

[treohr]

John,

That worked, thanks!

-Tyler