Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / May 2005

Tip: Looking for answers? Try searching our database.

Adding separate accumulators for multiple cells

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jrambo63 - 26 May 2005 17:19 GMT
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!
Bernie Deitrick - 26 May 2005 18:56 GMT
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

> I've learned to add an accumulator to multiple cells using the
> code on http://www.mcgimpsey.com/excel/accumulator.html
[quoted text clipped - 27 lines]
>
> Thanks!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.