Excel Accumulator on two columns

0

There is a well known code for accumulating values in cells in excel which is:

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("B1").Value = Range("B1").Value + .Value
               Application.EnableEvents = True
            End If
         End If
      End With 
End Sub
    

the value input in the first cell gets added to the second cell where it is stored and summed with all the values a user inputs in the first cell, I'd like to make it work for all cells in two columns, let's say column F and G, values input in a cell in the F column get added to the correspoding cell in the G column.

I tried and looked for a solution but it's beyond me, anybody can make it happen?

Thanks a lot!

Answer
Discuss

Answers

0
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.

Discuss

Discussion

just brilliant! thanks a lot!
wolbet (rep: 2) Dec 21, '19 at 5:31 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login