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! |
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! |
All times are GMT +1. The time now is 09:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com