当公式正常工作时，VBA返回"type-mismatch“

Question

我有一个宏，如果E列中的值与A列、B列和D列中的值匹配，它将返回E列中的值。下面的公式输入到Excel中，效果很好：

=SUMPRODUCT(--(A1:A180="Category Name"),--(B1:B180="Specific Type"),--(D1:D180=2015),(E1:E180))

但是下面的VBA程序返回一个“类型不匹配错误”。该程序位于一个模块中，而不是位于特定的工作表下。我已经验证了“工作表名称”范围也存在。有没有人能看出来错误是什么？

对于记录，列a和b是文本，列d和e是数字。

Function LocateFirstCell()
Dim FirstCell

Dim ColA
Dim ColB
Dim ColD
Dim ColE

Dim Name As String
Dim BOD As String
Dim Year As Integer

Set ColA = Worksheets("Sheet Name").Range("A1:A180")
Set ColB = Worksheets("Sheet Name").Range("B1:B180")
Set ColD = Worksheets("Sheet Name").Range("D1:D180")
Set ColE = Worksheets("Sheet Name").Range("E1:E180")


Name = "Category Name"
BOD = "Specific Type"
Year = 2015


FirstCell = [SUMPRODUCT(--(ColA=Name),--(ColB=BOD),--(ColD=Year),(ColE))]
'=SUMPRODUCT(--(A1:A180="Category Name"),--(B1:B180="Specific Type"),--(D1:D180=2015),(E1:E180))

MsgBox ("FirstCell is " & FirstCell)
End Function

Answer 1

不能在Evaluate的缩写中使用vba变量；[]是ActiveSheet.Evaluate的缩写

您必须将其写出来，并将变量连接成一个字符串：

FirstCell = Worksheets("Sheet Name").Evaluate("SUMPRODUCT(--(" & ColA.Address & "=""" & Name & """),--(" & ColB.Address & "=""" & BOD & """),--(" & ColD.Address & "=" & Year & "),(" & ColE.Address & "))")