LadyDayo
asked on
Running Total in Single Cell for multiple columns
I have this macro which works great but how do I get it to repeat for each row.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Range("c3").Select
Range("d3").Select
Range("e3").Select
Range("f3").Select
Dim cel As Range, targC As Range, tarD As Range, targE As Range, targF As Range
Set targC = Intersect(Range("h3"), Target)
Set targD = Intersect(Range("i3"), Target)
Set targE = Intersect(Range("j3"), Target)
Set targF = Intersect(Range("k3"), Target)
On Error Resume Next
Application.ScreenUpdating = False
If Not targC Is Nothing Then
For Each cel In targC
Range("c3") = Range("c3") + Range("h3")
'Cells(cel.Row, 8) = Cells(cel.Row, 8) + cel 'Increment total in column H, same row
Next
End If
If Not targD Is Nothing Then
For Each cel In targD
Range("d3") = Range("d3") + Range("i3")
'Cells(cel.Row, 9) = Cells(cel.Row, 9) + cel 'Increment total in column I, same row
Next
End If
If Not targE Is Nothing Then
For Each cel In targE
Range("e3") = Range("e3") + Range("j3")
'Cells(cel.Row, 10) = Cells(cel.Row, 10) + cel 'Increment total in column J, same row
Next
End If
If Not targF Is Nothing Then
For Each cel In targF
Range("f3") = Range("f3") + Range("k3")
'Cells(cel.Row, 11) = Cells(cel.Row, 11) + cel 'Increment total in column K, same row
Next
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Range("c3").Select
Range("d3").Select
Range("e3").Select
Range("f3").Select
Dim cel As Range, targC As Range, tarD As Range, targE As Range, targF As Range
Set targC = Intersect(Range("h3"), Target)
Set targD = Intersect(Range("i3"), Target)
Set targE = Intersect(Range("j3"), Target)
Set targF = Intersect(Range("k3"), Target)
On Error Resume Next
Application.ScreenUpdating
If Not targC Is Nothing Then
For Each cel In targC
Range("c3") = Range("c3") + Range("h3")
'Cells(cel.Row, 8) = Cells(cel.Row, 8) + cel 'Increment total in column H, same row
Next
End If
If Not targD Is Nothing Then
For Each cel In targD
Range("d3") = Range("d3") + Range("i3")
'Cells(cel.Row, 9) = Cells(cel.Row, 9) + cel 'Increment total in column I, same row
Next
End If
If Not targE Is Nothing Then
For Each cel In targE
Range("e3") = Range("e3") + Range("j3")
'Cells(cel.Row, 10) = Cells(cel.Row, 10) + cel 'Increment total in column J, same row
Next
End If
If Not targF Is Nothing Then
For Each cel In targF
Range("f3") = Range("f3") + Range("k3")
'Cells(cel.Row, 11) = Cells(cel.Row, 11) + cel 'Increment total in column K, same row
Next
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks, LadyDayo.
I really didn't expect to get your requirements right first time - however, I keep an eye on "my" questions for a number of weeks after they're closed, so please feel free to post here if you have any issues.
Regards,
Brian.
I really didn't expect to get your requirements right first time - however, I keep an eye on "my" questions for a number of weeks after they're closed, so please feel free to post here if you have any issues.
Regards,
Brian.
Could please post a sample workbook so we can see the macro in action and what you would like achieve
Michael