MAKE CELL KEEP A TOTAL FROM VARY DATA

Closed
Report
-
 macca -
How can you make a cell keep a running total when any number is entered in to another cell? ie enter a number in A3, A1 keeps total enter another number in A3 and cell A1 increases by that amount plus original amount.

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Are you saying that A1 should keep on adding when ever a new value is entered in it ? So
A3 =0, -------------A1=0
A3=5, -------------A1=0+5=5
A3=20-------------A1=5+20=25
and so on ?
0
yes thats right
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Assumptions:
1. The calculation and changed cell is on same sheet
2. The note in the code has been read

Steps

1. Press ALT + F11
2. Press CTRL + R
3. Double click on the sheet where this calculation will occur
4. Paste the code

Dim lCurrentValue As Variant
Dim bChanged As Boolean


Private Sub Worksheet_Change(ByVal Target As Range)

    If ((bChanged = True) And (Target.Address = "$A$3")) Then
        
        ' NOTE: this check is to trap situation where the last value in the cell
        ' was same as current value. Remove this IF and END IF
        ' if that is not what is required
        If (Target <> lCurrentValue) Then
            Range("A1") = Range("A1") + Target
        End If
        
    End If
    
    
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    bChanged = (Target.Address = "$A$3")
    If bChanged Then
        lCurrentValue = Target
    End If
    
End Sub
0
thank i will try this
0