vba - Excel-VBA中的汇总

我在vba中的sumifs有一些问题：

Dim Arg1 As Range 'the range i want to sum
Dim Arg2 As Range 'criteria range
Dim Arg3 As Variant 'the criteria

Set Arg1 = ThisWB.Sheets("Sheet1").Range("B2:B100")
Set Arg2 = ThisWB.Sheets("Sheet1").Range("C1:C100")
Set Arg3 = ThisWB.Sheets("Sheet2").Range("A2:A12")

For i = 2 To 12
Workbooks("x.xlsx").Worksheets("Sheet2").Cells(i, LastColumn) _
= Application.WorksheetFunction.SumIfs(Arg1, Arg2, Arg3)


Next

我总是收到“类型不匹配”错误

有人可以帮我修复代码吗？

先感谢您。

最佳答案

https://msdn.microsoft.com/en-us/library/office/ff193011.aspx

Sub test()
    Dim Arg1 As Range 'the range i want to sum
    Dim Arg2 As Range 'criteria range
    Dim Arg3 As Variant 'the criteria

    'Arg1 and Arg2 must be the same size
    Set Arg1 = Sheets("Sheet1").Range("B2:B100")
    Set Arg2 = Sheets("Sheet1").Range("C2:C100")

    'this is the criteria
    Arg3 = "=False"

    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim i As Integer
    For i = 2 To 12
        ws.Cells(i, 8).Value = Application.WorksheetFunction.SumIfs(Arg1, Arg2, Arg3)
    Next
End Sub

您也可以将Arg3指定为变体，并在有条件的情况下通过单细胞范围。条件可以是True / False（= False），数字（20）或字符串（“> 100”）。

    Dim Arg3 As Variant 'the criteria
    Arg3 = Sheets("Sheet2").Range("A2")

编辑：我意识到你想要做什么。 Arg3中的每个单元格都是您要执行SumIf的单独条件。这是修改后的代码。

Sub test2()
    Dim ThisWB As Workbook: Set ThisWB = ThisWorkbook
    Dim i As Integer
    Dim LastColumn As Integer: LastColumn = 3

    Dim Arg1 As Range 'the range i want to sum
    Dim Arg2 As Range 'criteria range
    Dim Arg3 As Range 'the criteria (range)

    Set Arg1 = ThisWB.Sheets("Sheet1").Range("B2:B100")
    Set Arg2 = ThisWB.Sheets("Sheet1").Range("C2:C100")
    Set Arg3 = ThisWB.Sheets("Sheet2").Range("A2:A12")

    For i = 2 To 12
        Workbooks("x.xlsx").Worksheets("Sheet2").Cells(i, LastColumn) _
            = Application.WorksheetFunction.SumIfs(Arg1, Arg2, Arg3.Cells(i - 1, 1).Value)
    Next
End Sub

注意在SumIfs Arg3.Cells(i - 1, 1).Value中如何使用Arg3。另请注意，Arg1和Arg2的大小必须相同。

关于vba - Excel-VBA中的汇总，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40796062/