arrays - Application.worksheetfunction.sumif 以内部数组作为参数

Question

我正在尝试构建一个子例程，它检查客户端列表中的某个条件，分离出满足条件的条目，然后平均每个名称的剩余条目。这就像用 VBA 做一个数据透视表。我不想使用数据透视表，因为将数据写入新工作表、刷新它并对其进行处理会给工具的速度增加不必要的负担。此外，所有数组都将保存在代码中，而不是写在工作表上。我几乎完成了代码，但它最终给了我一个错误，我使用的是 sumif 条件。 澄清一点：参数'Number'是在主工具中声明的全局变量，它来自主列表中的名称计数，该列表位于sheet5中。我希望代码除此之外是不言自明的。 我在运行代码时得到的是错误 -运行时错误'1004'：对象'_Global'的方法'范围'失败在线TaskArray(k, 1) = Application.WorksheetFunction.SumIf(Range(Names), NewList(k), Range(ParameterB))

编码-

Sub Task()

Dim Names() As Variant                                                 'Declare Names 
ReDim Names(0 To Number) As Variant                               'Declare Names as a vector
Dim ParameterA() As Variant                                           'Declare Parameter A
ReDim ParameterA(0 To Number) As Variant                         'Declare Parameter A as a vector
Dim ParameterB() As Variant                                           'Declare Parameter B
ReDim ParameterB(0 To Number) As Variant                         'Declare Parameter B as a vector


Dim i As Integer

For i = 1 To Number

Select Case Sheet5.Range("BO" & i + 1) - Sheet5.Range("BN" & i + 1)
Case 0
Names(i) = ""
ParameterA(i) = Sheet5.Range("BN" & i + 1) - Sheet5.Range("BL" & i + 1)
ParameterB(i) = ""
Case Else
Names(i) = Sheet5.Range("F" & i + 1)
ParameterA(i) = Sheet5.Range("BN" & i + 1) - Sheet5.Range("BL" & i + 1)
ParameterB(i) = Sheet5.Range("BO" & i + 1) - Sheet5.Range("BN" & i + 1)
End Select

Next i


Sheet3.Range("T159") = Application.WorksheetFunction.Sum(ParameterA())        'Write the total of Parameter A
Sheet3.Range("T160") = Application.WorksheetFunction.Sum(ParameterB())        'Write the total of Parameter B
'________________________ To isolate the list of Names (Unique) with existent Parameter B 
Dim NewList() As Variant

Dim j As Long
Dim d As Scripting.Dictionary
Set d = New Scripting.Dictionary
With d
    For j = LBound(Names) To UBound(Names)
        If IsMissing(Names(j)) = False Then
            .item(Names(j)) = 1
        End If
    Next
    NewList = .Keys
End With
'________________________To create an array of sums of Parameter B
For k = 1 To Application.WorksheetFunction.CountA(NewList) - 1
Dim TaskArray() As Variant
ReDim TaskArray(1 To k, 0 To 1) As Variant
ReDim Names(0 To Number) As Variant
ReDim ParameterB(0 To Number) As Variant
TaskArray(k, 0) = NewList(k)
TaskArray(k, 1) = Application.WorksheetFunction.SumIf(Range(Names), NewList(k), Range(ParameterB))
Sheet19.Range("H" & k + 1) = TaskArray(k, 0)
Sheet19.Range("I" & k + 1) = TaskArray(k, 1)
Next k

End Sub

标签： arraysexcelvba