VBA SUMIF based on Variables

Question

I'm new to VBA and trying to figure this one out, I did not work with functions yet. I'm trying to automate the SUMIF formula on my VBA sheet. The Rec sheet will contain a dynamic SUMIF formulas based on a variable/range in Column D based on Column B values. the SUM range will be coming from the CB sheet, matching column K and summing column L. The following code does not return any values for the SUMIF functions, no errors to report.

Data Sample in the image below:

https://i.stack.imgur.com/VvDfw.png

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim ws As Worksheet
    Dim NoCol As Integer, NoRow As Integer
    Dim CritRng As Range, SumRng As Range

    Application.ScreenUpdating = False

    Set ws = Worksheets("Rec")

    With ws
        NoRow = .Cells(.Cells.Rows.Count, 3).End(xlUp).Row
        NoCol = .Cells(3, .Cells.Columns.Count).End(xlToLeft).Column
        Set CritRng = Sheets("CB").Range("k:k")
        Set SumRng = Sheets("CB").Range("L:L")
    End With

    For r = 3 To NoRow
        Cells(r, NoCol) = WorksheetFunction.SumIf(CritRng, Cells(r, 1), SumRng)
    Next r

    Application.ScreenUpdating = True

End Sub
```

Hi Musa, please post a link to a sample file and explain why would you want this in the selection_change event — Ricardo Diaz
Hi Ricardo, I want this procedure to be running continuously, I thought selection change would be the best approach. I'm trying to understand the SUMIF function for a dynamic range in VBA in different sheets in order to apply the logic to other projects I'm working on. Please find the attached. [link] (drive.google.com/open?id=1UqrklOpkvP7DHHBM_hL084OS2NliS7YU) — MMMM
Hi Ricardo, I was hoping that you had any update for me, regarding whats going wrong with the code, thanks a lot! — MMMM
Hi Musa, can you describe with a sample what the SumIf should return and why you need it to be calculated in every sheet change? Are you planning to add new account numbers and that the sumif formula is calculated each time? why not have a Excel Table and let it do the formula filling for you? — Ricardo Diaz

Ricardo Diaz Ricardo Diaz · Accepted Answer · 2020-01-31T14:01:54

1. How to use the SUMIF function

I suggest that you always use the SUMIFS function, it's more flexible as it allows you to have multiple conditions

As per the documentation the syntax for the SUMIFS function is:

expression.SumIfs (Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

Where:

Arg1 is the type of Range and corresponds to Sum_range - the range to sum

Arg2 is the type of Range and corresponds to Criteria_range1, criteria_range2... - One or more ranges in which to evaluate the associated criteria

Arg3 - Arg30 is the type of Variant and corresponds to Criteria1, criteria2... - One or more criteria in the form of a number, expression, cell reference, or text that define which cells will be added

2. About your code

You want to use the Change event which is different from SelectionChange

The first one triggers when a cell or range is changed and the second when you change your selection either with you mouse, keyboard or other VBA code.

Using the Change event

You have a Target argument that refers to the cell or range that has changed in the worksheet
You need to restrict the monitored changed cells to a certain range
You want to to something with the changed range or other cells in the worksheet

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim monitoredRange As Range
    Dim cell As Range

    Dim sumRange As Range
    Dim criteriaRange As Range

    Dim sumOffsetColumn As Long
    Dim sumFirstRow As Long
    Dim sumLastRow As Long

    ' Initialize variables
    sumOffsetColumn = 2 ' How many columns offset from the Target (changed) cell
    sumFirstRow = 4
    sumLastRow = 1000

    Set monitoredRange = Me.Range("B:B") ' Here you can restrict this to a certain row

    ' Check if changed cell/range is not in the monitored range exit the procedure
    If Intersect(Target, monitoredRange) Is Nothing Then Exit Sub

    ' Set the sumifs parameters
    Set sumRange = Sheet1.Range("L" & sumFirstRow & ":L" & sumLastRow) ' Use sheet's codename (Sheet1)
    Set citeriaRange = Sheet1.Range("K" & sumFirstRow & ":K" & sumLastRow) ' Use sheet's codename (Sheet1)

    ' Apply to each cell in target range
    For Each cell In Target.Cells

        ' Check that the cell is not null
        If cell.Value <> vbNullString Then


            ' Set the value to the conditional sum
            cell.Offset(0, sumOffsetColumn).Value = Application.WorksheetFunction.SumIfs(sumRange, citeriaRange, cell.Value)

        End If

    Next cell

End Sub

3. The Excel (structured) tables approach

I often see that people try to reinvent the wheel by using VBA. In a large number of cases you don't need to code your solutions because the Excel team has incorporated the functionality in the program itself.

In this case you can use Excel Tables

Add headers to the columns (in your case the last column lacks a header)

Select the range that will gather the information

Click on Home | Format as table | | OK (make sure table has header is checked)

Rename the table (with table selected Ribbon | Table design | Table name

To

Delete the values you have in the sum column

6 . Add the following formula to the first cell in the SUM column

=SUMIFS(CB!L:L;CB!K:K;[@[Account '#]])

Press enter and see that the formula is copied to the whole column

Now try to add a new Account # and see that the formula is filled to the new cells

Let me know if it works

VBA SUMIF based on Variables

1 Answers