Sumproduct VBA返回类型不匹配错误

Question

我是VBA的新手，所以我确信我缺少的东西非常简单，经验丰富的人不会忽视。

我的代码如下：

Sub test_match()
Sheets("Course Cascade").Select
Dim i As Long
Dim r As Long
Dim c As Long
Dim rfirst As Long
Dim cfirst As Long
Dim rlast As Long
Dim colnum As Long
Dim rownum As Long
Dim csum As Long
Dim rowTerm As Long
Dim k As Integer
Dim rabove As Long
Dim ccount As Long
Dim clkup As Long
r = 107: c = 7: rowTerm = 106: ccount = 3: cfirst = 7: csum = c - 1: k = WorksheetFunction.CountIf(Range("e107:e1661"), ">0"): clkup = 5
If Cells(r, ccount) <= WorksheetFunction.SumProduct((Range(Cells(47, c), Cells(74, c))), (--Range("CourseCascadeRow").Value = Cells(r, clkup))) Then
Cells(r, c) = WorksheetFunction.IfError(WorksheetFunction.Index("CourseCascadeCt", WorksheetFunction.Match(Range(Cells(r, 5)), Range("CourseCascadeCtRow"), 0), _
WorksheetFunction.Match(Range(Cells(106, c)), Range("CourseCascadeCol"), 0) / WorksheetFunction.Index("CourseCascadeCt", WorksheetFunction.Match(Range(Cells(r, 5)), Range("CourseCascadeCtRow"), 0), _
WorksheetFunction.Match(Range(Cells(106, c)), Range("CourseCascadeCol"), 0))), 0)

还有一个其他参数，但我已经将类型不匹配错误与Sumproduct公式的这一部分隔离开来：--Range("CourseCascadeRow"(.Value=Cells(r，clkup((。范围末尾的".value"是我尝试添加和删除的内容，没有任何更改。

有人能告诉我我做错了什么吗？我知道代码很难看。。。

谢谢！

Answer 1

@TimWilliams评论的BTW，将其拆分并限制工作表函数的数量：

Sub kjlkjlkj()
Dim CCR As Variant
CCR = Range("CourseCascadeRow")
Dim vl As Double
vl = 0#

Dim i As Long
For i = 1 To UBound(CCR, 1)
Dim j As Long
For j = 1 To UBound(CCR, 2)
vl = vl + (Cells(47, c) * .Cells(74, c) * (-1 * CBool(CCR(i, j) = Cells(r, clkup))))
Next j
Next i

If .Cells(r, ccount) <= vl Then
Dim rw As Variant
rw = Application.Match(Range(Cells(r, 5)), Range("CourseCascadeCtRow"), 0)
Dim clm As Variant
clm = Application.Match(Range(Cells(106, c)), Range("CourseCascadeCol"), 0)
Cells(r, c.value = 0
If Not (IsError(rw) Or IsError(clm)) Then
Cells(r, c).Value = Range("CourseCascadeCt").Cells(rw, c).Value / Range("CourseCascadeCt").Cells(rw, c).Value
End If
End If
End Sub

这将有助于调试。