excel - 将 SumProduct 函数转换为 VBA

Question

我正在尝试编写包含以下行的代码:

WorksheetFunction.SumProduct((Columns(3) = ActiveCell.Value) + 0)

代码总是返回类型不匹配错误!

我正在使用那行代码来避免使用长度超过 255 个字符串的 Countif 函数。

提前致谢

这是原始代码:

Dim MyColumn As Long, r As Long, lngLastRow As Long
MyColumn = ActiveCell.Column
With Sheets("Project Breakdown")
lngLastRow = .Cells(.Rows.Count, MyColumn).End(xlUp).Row
    For r = lngLastRow To 1 Step -1
        If InStr(1, Cells(r, MyColumn - 2), "DIV", vbTextCompare) = 0 And InStr(1, Cells(r, MyColumn - 2), "SEC", vbTextCompare) = 0 Then
             If WorksheetFunction.CountIf(.Columns(MyColumn), .Cells(r, MyColumn).Value) > 1 Then
            .Cells(r, MyColumn).Delete Shift:=xlUp
             End If
        End If
    Next r
End With

由于字符串长度超过 255，countif 返回错误，因此我尝试使用 sumproduct 函数，但无法使其正常工作。

Answer 1

问题是您将 Range 与单个值进行比较。
这适用于excel公式，但不适用于vba。

一种解决方法是在工作表中使用额外的列，例如

=if($C1='some value',1,0)

然后取此列的总和。

或者，您可以在 excel 中计算 sumproduct。在预定义的单元格(例如 A1)中写入事件单元格的值，并让 sumproduct 使用以下公式:

=sumproduct((C:C=$A$1)+0)

更新:
假设 A1 是一个空闲单元格，A2 包含以下公式:

=SumProduct((C:C=$A$1)+0)

通过将 A1 的值更改为当前单元格，此公式包含您的结果。

Dim MyColumn As Long, r As Long, lngLastRow As Long
MyColumn = ActiveCell.Column
With Sheets("Project Breakdown")
lngLastRow = .Cells(.Rows.Count, MyColumn).End(xlUp).Row
    For r = lngLastRow To 1 Step -1
        If InStr(1, Cells(r, MyColumn - 2), "DIV", vbTextCompare) = 0 And InStr(1, Cells(r, MyColumn - 2), "SEC", vbTextCompare) = 0 Then
             .cells(1,1).value = .cells(r, MyColumn).value
             ' Might be necessary to call .Calculate here to assure that A2 contains its new value
             If .cells(1,2) > 1 Then
            .Cells(r, MyColumn).Delete Shift:=xlUp
             End If
        End If
    Next r
End With