Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Adding separate accumulators for multiple cells
I've learned to add an accumulator to multiple cells using the
code on http://www.mcgimpsey.com/excel/accumulator.html Private Sub Worksheet_Change(ByVal Target As Excel.Range) Static dAccumulator As Double With Target If Not Intersect(.Cells, Range("C8:O9")) Is Nothing Then If Not IsEmpty(.Value) And IsNumeric(.Value) Then dAccumulator = dAccumulator + .Value Else dAccumulator = 0 End If Application.EnableEvents = False .Value = dAccumulator Application.EnableEvents = True End If End With End Sub I was able to get it working for the cells I needed, but I would like accumulators running on multiple cells (all running a separate accumulator). What do I need to change in the code to accomplish this? The cells I need this for are C8 through O8 & C9 through O9 As it is set up now if I enter the value "5" into cell C8 and then add another "5" into cell C8 then the new value is "10", which is great. But when I also try to enter the value "5" into cell C9, the new value becomes "15" instead of "5". Thanks! |
#2
|
|||
|
|||
jrambo63,
Try this code: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim oldValue As Double Dim newValue As Double If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("C8:O9")) Is Nothing Then Exit Sub If Not IsEmpty(Target.Value) And IsNumeric(Target.Value) Then Application.EnableEvents = False newValue = Target.Value Application.Undo oldValue = Target.Value Target.Value = oldValue + newValue Application.EnableEvents = True End If End Sub HTH, Bernie MS Excel MVP "jrambo63" wrote in message ups.com... I've learned to add an accumulator to multiple cells using the code on http://www.mcgimpsey.com/excel/accumulator.html Private Sub Worksheet_Change(ByVal Target As Excel.Range) Static dAccumulator As Double With Target If Not Intersect(.Cells, Range("C8:O9")) Is Nothing Then If Not IsEmpty(.Value) And IsNumeric(.Value) Then dAccumulator = dAccumulator + .Value Else dAccumulator = 0 End If Application.EnableEvents = False .Value = dAccumulator Application.EnableEvents = True End If End With End Sub I was able to get it working for the cells I needed, but I would like accumulators running on multiple cells (all running a separate accumulator). What do I need to change in the code to accomplish this? The cells I need this for are C8 through O8 & C9 through O9 As it is set up now if I enter the value "5" into cell C8 and then add another "5" into cell C8 then the new value is "10", which is great. But when I also try to enter the value "5" into cell C9, the new value becomes "15" instead of "5". Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separate first and second name in one cell into separate cells. | Excel Discussion (Misc queries) | |||
Separate first and second name in one cell into separate cells. | Excel Discussion (Misc queries) | |||
Adding a lot of text to MS Excel cells | Excel Discussion (Misc queries) | |||
Pasting CSV list into separate cells... | Excel Discussion (Misc queries) | |||
Adding rows of different info from separate worksheets into summar | New Users to Excel |