如何使用 VBA 管理 sumproduct？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24341475/
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
How to manage sumproduct with VBA?
提问by user3762804
I wrote the following code: I'm trying to use sumproduct function with vba but it doesn't work. I've tried with evaluate function but I got the same bad result.
我写了以下代码：我试图在 vba 中使用 sumproduct 函数，但它不起作用。我尝试过使用评估功能，但得到了同样糟糕的结果。
Dim row As Long
Dim rows As Range
Dim us As Integer
Dim rng As Range
Dim rngl As Range
Dim rngo As Range
Dim rngp As Range
With Sheets("Report")
LastRow = .Range("C" & .rows.Count).End(xlUp).row
End With
Set rng = ThisWorkbook.Sheets("REPORT").Range("g3:g" & LastRow)
Set rngl = ThisWorkbook.Sheets("REPORT").Range("F3:f" & LastRow)
Set rngo = ThisWorkbook.Sheets("REPORT").Range("c3:c" & LastRow)
For us = 3 To 33
Range("e" & us).Value = Application.Evaluate("=SUMPRODUCT((sheets(luglio).Range(c & us))<rng)*(sheets(luglio).Range(c & us)=rngl)")
Next us
回答by Quarcheek
As Rory mentions in the comments, this isn't working because VBA is passing the entire string inside the quotations marks to Excel. You need to concatenate the strings to get the desired output as you would see in the Excel formula.
正如 Rory 在评论中提到的，这不起作用，因为 VBA 将引号内的整个字符串传递给 Excel。您需要连接字符串以获得所需的输出，就像您在 Excel 公式中看到的那样。
Try replacing the line:
尝试替换该行：
Range("e" & us).Value = Application.Evaluate("=SUMPRODUCT((sheets(luglio).Range(c & us))<rng)*(sheets(luglio).Range(c & us)=rngl)")
With:
和：
Range("e" & us).Value = "=SUMPRODUCT((luglio!C" & us & "<REPORT!G3:G" & LastRow & ")*(luglio!C" & us & "=REPORT!F3:F" & LastRow & "))"
