Selected Answer
The code below will do what you want.
Option Explicit
Private Enum Nws ' worksheet navigation
' modify to match your requirements
NwsFirstRow = 2 ' changes to rows above NwsFirstRow are ignored
NwsTriggerClm = 6 ' numbers added in this column are added to NwsStoreClm
' Column 6 = column F
NwsStoreClm ' no assigned value means: previous + 1
' here = 7 = column G
End Enum
Private Sub Worksheet_Change(ByVal Target As Range)
' Variatus @TeachExcel.com
' 21 Dec 2019
Dim Cell As Range
With Target
If (.Columns.CountLarge > 1) Or _
(.Column <> NwsTriggerClm) Or _
(.Row < NwsFirstRow) Then Exit Sub
End With
For Each Cell In Target
With Cell
If IsNumeric(.Value) Then
Application.EnableEvents = False
Cells(.Row, NwsStoreClm).Value = Cells(.Row, NwsStoreClm).Value + .Value
Application.EnableEvents = True
End If
End With
Next Cell
End Sub
Treat the enumeration at the top of the sheet as a sort of switchboard where you can set different columns and exclude the first row, for example, if it contains titles. Remember that the Enum must always be at the top of the sheet, before any procedures. Its purpose is to assign names to the values which are then available to all procedures in the module.
The code is designed not to respond to changes in row 1 (change in the enum) and ignore non-numeric entries (rather than evaluating them to zero). This will work to avoid the macro writing in blank rows at the bottom of the sheet but not effectively prevent it.
The code allows you to paste a series of numbers to the trigger column simultaneously and update the storage column. However, if you were to paste column A:B to column F existing values in column G would be over-written by your pasting action. The macro wouldn't update column G because it can't process a pasted range of more than one column.
