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" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

I put this VB code into my Workbook and all of the cells that I want to accumulate are doing just that. But, I also want to link these cells to another workbook. When I go to put the formula to do this into the respective cells, the number updates, but the formula does not stay in the cell so the next time the number is changed in the other workbook it will update. What is going on? Is the VB code causing it to do this, and is there a way to fix it? Thank you.

Very sorry, I was wondering about that. I apologize and will do just that. thank you!