Is It Possible?

  • Thread starter Thread starter documike
  • Start date Start date
D

documike

Is it possible to have a cell that's accumulative...in other words, if I
have a cell that has a value of 4 and I want to add 2 to it by typing in the
"2" and have it now have a value of 6? I'm trying to avoid having a list
that adds and then link the total cell. I've never done this and don't
recall ever seeing it done.
 
While I personally am not a fan of doing this as there is no audit trail to
determine how the number was arrived at here is some code to do it for you...
right click the sheet tab where you want the accumulator and paste this code
(it works for cell A1)...

Private dblAccumulator As Double

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Errorhandler

Application.EnableEvents = False
If Target.Address = "$A$1" Then Target.Value = Target.Value +
dblAccumulator
Errorhandler:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then dblAccumulator = Target.Value
End Sub
 
I tried this...however it does not total the amounts, only 4 + 2 etc.
instead of 6
I guess I could manually do a =SUM( at the end of entries but I'm trying to
automate as much as possible.
 
I actually have several cells that I need to do this...can I simply use
something like this?:

If Target.Address = "$A$1","$C$14","$D$3 Then Target.Value = Target.Value +
 
A little trickier than that...

Private dblAccumulator As Double

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Errorhandler

Application.EnableEvents = False
Select Case Target.Address
Case "$A$1", "$B$1", "$C$1"
Target.Value = Target.Value + dblAccumulator
End Select
Errorhandler:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$A$1", "$B$1", "$C$1"
dblAccumulator = Target.Value
End Select
End Sub
 
Back
Top