In cells addition

[holy_eti]

Hello everyone,

I would like to know if there is anyway to do in cells addition. As an exemple, the value of the cell A2 is 2. When I put another value in cell A2 exemple 3, I would like the value of cell A2 to be 5 and not 3. So an addition of the old value and the new value. I can deal either with VBA or the Sheet itself. The persons who's gonna use this isn't a computer friendly user so he does'nt know a lot about computer. I only want him to change numbers and no formula to change.

Thank you

 

[holy_eti]

It is working!!! Here's another problem: If the operator make a mistake, is there an easier way to erase the previous number or to reset the number to 0. I Know that there is the break and erase mode, but that will be quite hard for somebody who isn't good with a computer. Would a command button that put vba on break mode work?

 

[Joe4]

There is an easy way to get back to zero without having to do any more programming.
Whatever number is currently showing, just type in the negative of that number, i.e.
If it is currently showing 25, just type -25

 

[holy_eti]

Well I wasn't expecting that, Thanks a lot!!

 

[Joe4]

Well I wasn't expecting that, Thanks a lot!!

You are welcome.

Yes, since we are just adding whatever we type in to whatever is currently in there, all we need to do to get back to zero is to enter the additive inverse of whatever number is currently showing there (which is just the negative equivalent of the displayed number).

 

[holy_eti]

I have another question, if I want to put the same formula for multiple cells, do I have to re-write the code for each cell or is there a way to put all the cells on a same code?
Thanks

 

[Joe4]

Here is an example of how you would do it for A2, B2, or C2:

Public StoredValue As Double


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim rng As Range
    Set rng = Intersect(Target, Range("A2:C2"))
    
'   Capture value of cell when it is selected
    If (Target.Count = 1) And (Not rng Is Nothing) Then
        If IsNumeric(Target.Value) Then StoredValue = Target.Value
    End If

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Set rng = Intersect(Target, Range("A2:C2"))
    
'   Add value of cell to previous value
    If (Target.Count = 1) And (Not rng Is Nothing) Then
        If IsNumeric(Target.Value) Then
            Application.EnableEvents = False
            Target.Value = Target.Value + StoredValue
            Application.EnableEvents = True
        End If
    End If
    
End Sub

 

[holy_eti]

It worked! Thank you very much @Joe4
I owe you one