Tom’s Tutorials for Excel:Running Sum in Same Cell
Here is a WorksheetChange event in VBA that allows any cell in column A to accept a number you enter, add it to whatever number was already in that same cell, and display the resulting sum. For example, if cell A9 currently holds the number 2 and you enter the number 3 in that cell, the resulting value of cell A9 will be 5.
To implement the code, right-click on your worksheet tab and select View Code. Paste the below code into the large white area that is the worksheet module. To get back to your worksheet, press the Alt+Q keyboard combination. To test the code, enter a number in any cell in column A, then enter another number in that same cell and see the resulting sum. To bring the cell back to a starting zero value, simply select the cell and press the Delete key.
Here is the code, with comments to explain what each line of code does.
Private Sub Worksheet_Change(ByVal Target As Range) 'Only apply this effect to column A (column 1 in VBA-Speak). 'At the same time, only allow one cell at a time to be changed. If Target.Column <> 1 Or Target.Cells.Count > 1 Then Exit Sub 'Pressing the Delete key triggers the Change event. 'You might want to delete the cell's contents and start with 'an empty cell, so exit the Change event if the Delete key is pressed. If IsEmpty(Target) Then Exit Sub 'Even though a number is *supposed* to be entered into column A, 'never assume that will always happen because users do make mistakes. 'Provide for the attempt at a non-numeric entry and disallow it. If IsNumeric(Target.Value) = False Then 'Disable events because you are about to undo the non-numeric value, 'and Undo also triggers the Change event. Application.EnableEvents = False 'Execute the Undo so the non-numeric entry is deleted. Application.Undo 'Enable events again. Application.EnableEvents = True 'Remind the user with a Message Box that only numbers are allowed, 'and exit the Change event procedure with the Exit Sub statement. MsgBox "You entered a non-numeric value.", _ vbExclamation, _ "Please: numbers only in column A!" Exit Sub End If 'Now that all the reasonable safeguards have been met, 'Declare two Double type variables: 'one named OldVal for the numeric value that was in the cell 'before it got changed, and the other variable named NewVal 'for the numeric value that was just entered. Dim OldVal As Double, NewVal As Double 'Define the NewVal variable first, as it is the number that 'was just entered into the cell. NewVal = Target.Value 'Undo the entry in order to display the old (preceding) value. 'Again, this requires that you disable events in order to not 're-trigger the Change event while you are already in a Change event. Application.EnableEvents = False 'Execute Undo so the previous value is re-established. Application.Undo 'Define the OldVal variable which is possible to do now that 'the previous value has been restored. OldVal = Target.Value 'Programmatically enter into the cell the sum of the old previous value, 'plus the new last-entered value, by referring to those two variables 'in an arithmetic equation just as you would if they were numbers. Target.Value = OldVal + NewVal 'Enable events now that all the changes to the cell have been made. Application.EnableEvents = True End Sub