SumIF 函数的 Excel VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23639195/
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
Excel VBA code for SumIF function
提问by ravi
Hi I am writing VBA code such that -
嗨，我正在编写 VBA 代码，以便 -
If the value of cell M1 is found in any cell Value of Range D1:D20 then Find the sum of corresponding cell values in Range C1:C20. But I am getting confused as to how to use the function and pass the parameters. This is what I have tried now, but it does not work
如果在范围 D1:D20 的任何单元格值中找到单元格 M1 的值，则在范围 C1:C20 中查找相应单元格值的总和。但是我对如何使用该函数和传递参数感到困惑。这是我现在尝试过的，但不起作用
Private Sub FindTotal()
Dim Temp2Sheet As Worksheet
Set Temp2Sheet = Sheets("Temp2")
Temp2Sheet.Activate
Dim temp As String
temp = Temp2Sheet.Range("M1").Value
Temp2Sheet.Range("N1").Value = Application.SumIf(Range("D1:D20"), "" * "&temp&" * "", Range("C1:C20"))
End Sub
I need to extend same logic for Range M1 to how many ever values are there is Column M and it is dynamic. The totals will appear in Column N in the corresponding cell of column M. Like M1 total will be in N1, and so on
我需要将范围 M1 的相同逻辑扩展到列 M 有多少个值，并且它是动态的。总计将出现在 M 列对应单元格的 N 列中。像 M1 总计将在 N1 中，依此类推
My main confusion here is how do I pass the temp variable as parameter 2 of SUMIF function and add wild cards (**)
我的主要困惑是如何将临时变量作为 SUMIF 函数的参数 2 传递并添加通配符 (**)
Any help is appreciated. Thanks in advance
任何帮助表示赞赏。提前致谢
采纳答案by Michael Blaustein
This is what I mean by triple quotes:
这就是我所说的三重引号的意思：
SumIf(Range("D1:D20"), """*" & temp & "*""", Range("C1:C20"))
回答by Tim Williams
This should do what you want, unless you're looking for something more than just finding basic substrings:
这应该可以满足您的需求，除非您正在寻找的不仅仅是查找基本子字符串：
Private Sub FindTotal()
With Sheets("Temp2")
.Range("N1").Value = Application.SumIf(.Range("D1:D20"), _
"*" & .Range("M1").Value & "*", _
.Range("C1:C20"))
End With
End Sub
回答by Manivannan KG
For looping concepts, Tim's code can be modified as
对于循环概念，Tim 的代码可以修改为
Dim rowIndex As Long
Dim sourceWorksheet As Worksheet
For rowIndex = 2 To lastRow
calcFormula = Application.SumIf(WorkSheet.Range("B:B"), _
"*" & Worksheet.Cells(rowIndex, "Q").Value & "*", _
WorkSheet.Range("L:L"))
Worksheet.Cells(rowIndex, "AS").Value = calcFormula ' output column
Next rowIndex
