Vfrdtyky

I am trying to build a subroutine which checks a certain condition in a client list, separates out the entries which meets the condition, and then average the remaining entries for each name. It is like a doing a pivot table with VBA. I did not want to use the pivot table, since writing the data into a new sheet, refresh it and do something with it adds an unnecessary burden on the speed of the tool. Furthermore, all the arrays are to be kept within the code, rather than written on the sheets. I am almost done with the code, but it is giving me an error in the very end, where I am using the sumif condition.
A point of clarification: The argument 'Number' is a global variable declared in the main tool, which comes from the count of names from the main list, which is in sheet5. I hope that the code is self explanatory beyond that.
What I am getting while running the code is the Error- Run-time error '1004':
Method 'Range' of object '_Global' failed on the line TaskArray(k, 1) = Application.WorksheetFunction.SumIf(Range(Names), NewList(k), Range(ParameterB))

The Code-

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