SUM Question

  • Thread starter Thread starter Sheldon
  • Start date Start date
S

Sheldon

In a programming language, if the variable "x" has a current value of
10, the expression
x = x + 2 will increment the value of x to 12 and x = x + 3 will
increment the value of x to 15 and so forth. Can that same logic be
done in Excel? e.g. If cell A1 currently has a value of 10 and I type
in a 2 in cell B1, can I increment the value in A1 to 12 and if I tab
over to C1 and then tab back to B1 and type in a 3, can the value in
A1 now reflect 15 and so forth. The goal is to not need to use a new
cell each time I want to type in a new value (loaves of bread in my
real life example). I only care that there are now 15 loaves of bread
in stock. I don't need to know that 2 loaves were delivered yesterday
once my inventory total was bumped up yesterday (to 12). Thanks for
any suggestions.

Sheldon Potolsky
 
Certainly other ways, but one way:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("B1").Address Then Range("A1").Value =
Range("A1").Value + Range("B1").Value
End Sub

Right-click the worksheet tab you want to apply this to, select "View Code",
and paste the above code. Everytime B1 is changed, A1 will increment.

HTH,
Paul
 
Sheldon

Just make note that there will be no way of checking for errors in input.

Once you have entered a number in B1, A1 will update even if the number is
incorrect.


Gord Dibben MS Excel MVP
 
Thank you Gord. I had noticed that as well which is making me rethink
the design that I thought I would use. I had tried UNDO and saw that
it had no effect on A1.

Sheldon Potolsky
 
There is no undo after a macro or event code without further coding to store
the input to have it available for the undo.

Excel has ample cells avilable for you to have an extra column with the
running total.


Gord
 
Back
Top