基于变量的VBA SUMIF [英] VBA SUMIF based on Variables

问题描述

我是VBA的新手，试图弄清楚这一点，我还没有使用函数.我正在尝试在VBA工作表上自动执行SUMIF公式.记录表将包含动态SUMIF公式，该公式基于D列中基于B列值的变量/范围.SUM范围将来自CB表，匹配列K和求和列L.以下代码不会为SUMIF函数返回任何值，也不会报告任何错误.

下图中的数据样本:

2. 选择将收集信息的范围

3. 单击主页|表格格式||确定(确保已选中表的标题)

4. 重命名表格(已选择表格功能区|表格设计|表格名称

收件人

5. 删除汇总列中的值

6.在SUM列的第一个单元格中添加以下公式

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

7. 按Enter键，然后将公式复制到整个列中

8. 现在尝试添加新的帐户编号，并确保公式已填充到新单元格中.

让我知道它是否有效

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
```

解决方案

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

1. You have a Target argument that refers to the cell or range that has changed in the worksheet
2. You need to restrict the monitored changed cells to a certain range
3. 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

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

2. Select the range that will gather the information

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

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

To

5. 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 '#]])

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

8. 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！