Excel-VBA 中的 Sumifs
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40796062/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sumifs in Excel-VBA
提问by Csaba Csorba
I have some problem with sumifsin vba:
我sumifs在 vba 中有一些问题：
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
I always get a "Type mismatch" error
我总是收到“类型不匹配”错误
Could anybody help me to fix the code?
有人可以帮我修复代码吗？
Thank you in advance.
先感谢您。
采纳答案by D_Bester
https://msdn.microsoft.com/en-us/library/office/ff193011.aspx
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
You can also specify Arg3 as a variant and pass a single-cell range if it has the criteria. Criteria can be True/False (=False), a number (20) or a string (">100").
您还可以将 Arg3 指定为变体并传递具有条件的单单元格范围。条件可以是 True/False (=False)、数字 (20) 或字符串 (">100")。
Dim Arg3 As Variant 'the criteria
Arg3 = Sheets("Sheet2").Range("A2")
EDIT: I realized what you were trying to do. Each cell in Arg3 is a separate criteria that you want to do SumIf on. Here is the revised code.
编辑：我意识到你想要做什么。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
Note how Arg3 is used in SumIfs Arg3.Cells(i - 1, 1).Value. Also note that Arg1 and Arg2 must be the same size.
请注意在 SumIfs 中如何使用 Arg3 Arg3.Cells(i - 1, 1).Value。另请注意，Arg1 和 Arg2 的大小必须相同。
