Quite often, a situation arises when we need to sum up (accumulate) several values sequentially entered into one cell:
Those. if, for example, you enter the number 1 in cell A5, then the number 1 should appear in B15. If you then enter the number 1 in A7, then 1 should appear in cell B22, and so on. In general, what accountants (and not only them) call a cumulative total.
You can implement such a storage cell-accumulator using a simple macro. Right-click on the sheet tab where cells A1 and B1 are located and select from the context menu Source text (Source Code). In the Visual Basic editor window that opens, copy and paste the simple macro code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "A1" Then If IsNumeric(.Value) Then Application.EnableEvents = False Range("A2").Value = Range("A2").Value + .Value Application.EnableEvents = True End If End If End With End Sub The addresses of cells A1 and A2, of course, can be replaced with your own.
If you need to track data entry and summarize not individual cells, but entire ranges, then the macro will have to be slightly changed:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("A1:A10")) Is Nothing Then If IsNumeric(Target.Value) Then Application.EnableEvents = False Target.Offset(0, 1).Value = Target.Offset(0, 1).Value + Target.Value Application.EnableEvents = True End If End If End Sub It is assumed that data is entered in the cells of the range A1:A10, and the entered numbers are summed up in the adjacent column on the right. If in your case it is not adjacent, then increase the shift to the right in the Offset operator – replace 1 with a larger number.
- What are macros, where to insert macro code in VBA, how to use them?