0

I want to keep running totals of certain parameters within a daily report type format. For example in cell E9 I have the running hours of a machine and in cell F9 I want to have the running hours 'to date'. Gary's student (member here) posted the following solution:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Range("E9"), Target) Is Nothing Then Exit Sub
    [F9] = [F9] + [E9]
End Sub

This works fine but I have several other parameters on the same sheet I want to do the same thing with, for example in E10 I want to record how much gas that machine has used and in F10 I want how much gas 'to date'. When I copy the above code and paste it with the cell values changed only the first instruction works.

CallumDA
  • 12,025
  • 6
  • 30
  • 52
Stemurdo
  • 1
  • 1
  • I am a bit puzzled as to why you need VBA to double the value of a cell instead of just Excel Formula. Can you link to Gary's answer? – Slai Dec 12 '16 at 10:28
  • I don't want the value to double, maybe I haven't explained it very well. Starting at 0, today I run a machine for 10 hours. I enter this into E9 and F9 automatically registers 10. Tomorrow I run it for 5 hours. when the sheet is filled in the 'today's hours' (E9) will be 5, but the 'to date' hours (F9 will be 15. F9 is accumulated hours. Gary's formula works fine I just need to know how to repeat it for other values on the same sheet. – Stemurdo Dec 12 '16 at 11:34
  • link here http://stackoverflow.com/questions/29780974/how-to-make-a-cumulative-sum-in-one-cell – Stemurdo Dec 12 '16 at 11:37
  • My bad I saw E as F – Slai Dec 12 '16 at 11:42

1 Answers1

0

Target is the cell(s) that changed, and Target(, 2) is the cell right of it:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Range("E9:E12"), Target) Is Nothing Then Exit Sub  ' change E9:E12 to the range of cells that are changed
    Target(, 2).Value2 = Target(, 2).Value2 + Target.Value2         ' the .Value2 parts are optional
End Sub

If by any chance more than one cells are changed at once, the above will result in error (for example copy-paste or Ctrl + Enter). It can be fixed by looping trough all cells in the Target Range:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Range("E9:E12"), Target) Is Nothing Then Exit Sub
    Dim cell As Range
    For Each cell In Target.Cells
        cell(, 2).Value2 = cell(, 2).Value2 + cell.Value2
    Next
End Sub
Slai
  • 22,144
  • 5
  • 45
  • 53