Object Required Error , Array

Question

I am getting the error 424 Object Required. I am very unfamiliar with arrays and assume it has something to do with that. I read a large number of the Object Required Error questions in this forum and tried the various suggestions, but none solved my issue.

At the top of the module

Dim velocityLookup As Scripting.Dictionary
Dim arrHolder As Variant
Const Velocity_Key_Col As Long = 10
Option Explicit

Custom SumIf for Arrays

Public Function SumIf(lookupTable As Variant, lookupColumn As Long, currentRow As Long, lookupValue As String, sumifColumn As Long) As Long
    Dim i As Long

    SumIf = 0

    For i = 1 To currentRow
        If lookupTable(i, lookupColumn) = lookupValue Then
            SumIf = SumIf + lookupTable(i, sumifColumn)
        End If
    Next i
End Function

Issue Line

        arrHolder(i, 18) = arrHolder(i, 24) - SumIf(arrHolder, 21, i, arrHolder(i, 21).Value, 26)

Entire Code

Sub Calculate_Click()

'******************* Insert a line to freeze screen here.
Dim wsMain As Worksheet
Dim wsQuantity As Worksheet
Dim wsVelocity As Worksheet
Dim wsParameters As Worksheet
Dim wsData As Worksheet
Dim lrMain As Long 'lr = last row
Dim lrQuantity As Long
Dim lrVelocity As Long
Dim lrParameters As Long
Dim lrData As Long
Dim i As Long 'Row Counter

'For Optimization Testing Only.
Dim MainTimer As Double
MainTimer = Timer

Set wsMain = Worksheets("Main Tab")
Set wsQuantity = Worksheets("Quantity Available")
Set wsVelocity = Worksheets("Velocity")
Set wsParameters = Worksheets("Parameters")
Set wsData = Worksheets("Data Input by Account")

lrMain = wsMain.Cells.Find(What:="*", SearchOrder:=xlByRows, Searchdirection:=xlPrevious).Row
lrQuantity = wsQuantity.Cells.Find(What:="*", SearchOrder:=xlByRows, Searchdirection:=xlPrevious).Row
lrVelocity = wsVelocity.Cells.Find(What:="*", SearchOrder:=xlByRows, Searchdirection:=xlPrevious).Row
lrParameters = wsParameters.Cells.Find(What:="*", SearchOrder:=xlByRows, Searchdirection:=xlPrevious).Row
lrData = wsData.Cells.Find(What:="*", SearchOrder:=xlByRows, Searchdirection:=xlPrevious).Row

Dim calcWeek As Long
calcWeek = wsParameters.Range("B3").Value

For i = 2 To 5 'lrQuantity
    With wsQuantity
        .Cells(i, 5) = .Cells(i, 1) & .Cells(i, 2)
        .Cells(i, 6) = .Cells(i, 1) & UCase(.Cells(i, 2)) & .Cells(i, 3)
    End With
Next i

wsData.Range(wsData.Cells(2, 1), wsData.Cells(lrData, 4)).Sort _
key1:=wsData.Range("A2"), order1:=xlAscending, Header:=xlNo

Dim tempLookup As Variant
For i = 2 To 5 'lrData
    tempLookup = Application.VLookup(wsData.Cells(i, 2), wsParameters.Range("Table5"), 2, False)
    If IsError(tempLookup) Then
        wsData.Cells(i, 3).Value = "Missing"
    Else
        wsData.Cells(i, 3).Value = tempLookup
    End If
Next i

For i = 2 To 5 'lrVelocity
    With wsVelocity
        .Cells(i, 10) = CStr(Trim(.Cells(i, 1) & .Cells(i, 4) & .Cells(i, 5) & .Cells(i, 9)))
        .Cells(i, 11) = .Cells(i, 6)
        .Cells(i, 12) = .Cells(i, 7)
        .Cells(i, 13) = .Cells(i, 8)
        .Cells(i, 14) = .Cells(i, 3)
        .Cells(i, 22) = .Cells(i, 1) & .Cells(i, 9)
    End With
Next i

wsVelocity.Range(wsVelocity.Cells(2, 1), wsVelocity.Cells(lrVelocity, 10)).Sort _
key1:=wsVelocity.Range("J2"), order1:=xlAscending, Header:=xlNo

BuildVelocityLookup wsVelocity, Velocity_Key_Col, velocityLookup

Dim indexVelocity1 As Range
Dim indexVelocity2 As Range
Dim matchVelocity1 As Range
Dim matchVelocity2 As Range

With wsVelocity
    Set indexVelocity1 = .Range(.Cells(2, 7), .Cells(lrVelocity, 7))
    Set indexVelocity2 = .Range(.Cells(2, 3), .Cells(lrVelocity, 3))
    Set matchVelocity1 = .Range(.Cells(2, 1), .Cells(lrVelocity, 1))
    Set matchVelocity2 = .Range(.Cells(2, 22), .Cells(lrVelocity, 22))
End With

Dim indexQuantity As Range
Dim matchQuantity As Range
With wsQuantity
    Set indexQuantity = .Range(.Cells(2, 4), .Cells(lrQuantity, 4))
    Set matchQuantity = .Range(.Cells(2, 6), .Cells(lrQuantity, 6))
End With

Dim ShipMin As Long
ShipMin = wsParameters.Cells(7, 2).Value

With wsMain
    .Range(.Cells(2, 9), .Cells(lrMain, 20)).ClearContents
    .Range(.Cells(2, 22), .Cells(lrMain, 47)).ClearContents
End With

arrHolder = wsMain.Range(wsMain.Cells(2, 1), wsMain.Cells(lrMain, 47))


For i = LBound(arrHolder) To lrMain

        Dim conUD As String 'con=concatenate
        'conUD = .Cells(i, 21) & .Cells(i, 4) & calcWeek
        conUD = arrHolder(i, 21) & arrHolder(i, 4) & calcWeek

        '.Cells(i, 21) = .Cells(i, 5) & .Cells(i, 3)
        arrHolder(i, 21) = arrHolder(i, 5) & arrHolder(i, 3)

        'If .Cells(i, 8) <> 0 Then
        '    .Cells(i, 9) = .Cells(i, 6) / .Cells(i, 8)
        'End If
        If arrHolder(i, 8) <> 0 Then
            arrHolder(i, 9) = arrHolder(i, 6) / arrHolder(i, 8)
        End If

        Dim velocityRow As Long
        If velocityLookup.Exists(conUD) Then
            velocityRow = velocityLookup.Item(conUD)
            tempLookup = wsVelocity.Cells(velocityRow, 11)

        '.Cells(i, 10).Value = tempLookup
        arrHolder(i, 10) = tempLookup

        tempLookup = wsVelocity.Cells(velocityRow, 14)

        '.Cells(i, 11).Value = tempLookup
        arrHolder(i, 11) = tempLookup


        'If .Cells(i, 9) > .Cells(i, 11) Then
        '    .Cells(i, 12).Value = Round((.Cells(i, 6) / .Cells(i, 11)) / .Cells(i, 10), 0.1)
        'End If
        If arrHolder(i, 9) > arrHolder(i, 11) Then
            arrHolder(i, 12) = Round((arrHolder(i, 6) / arrHolder(i, 11)) / arrHolder(i, 10), 0.1)
        End If

        'If .Cells(i, 6) > 0 Then
        '    If .Cells(i, 12) <> "" Then
        '        .Cells(i, 13).Value = .Cells(i, 12) - .Cells(i, 8)
        '    End If
        'End If
        If arrHolder(i, 6) > 0 Then
            If arrHolder(i, 12) <> vbNullString Then
                arrHolder(i, 13) = arrHolder(i, 12) - arrHolder(i, 8)
            End If
        End If

        Dim conECD As String

        'conECD = .Cells(i, 5) & .Cells(i, 3) & .Cells(i, 4) & calcWeek
        conECD = arrHolder(i, 5) & arrHolder(i, 3) & arrHolder(i, 4) & calcWeek

        ' It looks like you use this block a few times with different variables. Consider extracting to a function
        If velocityLookup.Exists(conECD) Then
            velocityRow = velocityLookup.Item(conECD)
            tempLookup = wsVelocity.Cells(velocityRow, 12)

        'If .Cells(i, 13) <> "" Then
        '    If tempLookup <> 0 Then
        '        .Cells(i, 14).Value = Int(.Cells(i, 13) / tempLookup)
        '    End If
        'End If
        If arrHolder(i, 13) <> vbNullString Then
            If tempLookup <> 0 Then
                arrHolder(i, 14) = Int(arrHolder(i, 13) / tempLookup)
            End If
        End If

            tempLookup = wsVelocity.Cells(velocityRow, 13)



        'If .Cells(i, 14) > tempLookup Then
        '    If .Cells(i, 14) <> "" Then
        '        .Cells(i, 15).Value = tempLookup
        '    End If
        'Else
        '    .Cells(i, 15).Value = .Cells(i, 14).Value
        'End If
        If arrHolder(i, 14) > tempLookup Then
            If arrHolder(i, 14) <> vbNullString Then
                arrHolder(i, 15) = tempLookup
            End If
        Else
            arrHolder(i, 15) = arrHolder(i, 14)
        End If

        'If .Cells(i, 14) = "" Then
        '    If .Cells(i, 11) = "" Then
        '        .Cells(i, 26) = ""
        '    Else
        '        .Cells(i, 26).Value = Round(.Cells(i, 14).Value * .Cells(i, 11).Value, 0)
        '    End If
        'End If
        If arrHolder(i, 14) = vbNullString Then
            If arrHolder(i, 11) = vbNullString Then
                arrHolder(i, 26) = vbNullString
            Else
                arrHolder(i, 26) = Round(arrHolder(i, 14) * arrHolder(i, 11), 0)
            End If
        End If


        'tempLookup = Application.Index(indexQuantity, Application.Match((.Cells(i, 21).Value & "LIBERTY") _
        '    , matchQuantity, False))
        Dim arrHolderRow As Long

        tempLookup = Application.Index(indexQuantity, Application.Match((arrHolder(i, 21) & "LIBERTY") _
            , matchQuantity, False))

        '.Cells(i, 24).Value = tempLookup
        arrHolder(i, 24) = tempLookup

        ' I havent used application SumIf on an array before, so I instead edited this so it should use the correct index value.
        ' This will likely not work as I want it to, so it may just need to go into a separate loop or something.
        ' .Cells(i, 18).Value = .Cells(i, 24) - Application.SumIf(.Range(.Cells(1, 21), .Cells(i, 21)), _
        '    .Cells(i, 21).Value, .Range(.Cells(1, 26), .Cells(i, 26)))
        arrHolder(i, 18) = arrHolder(i, 24) - SumIf(arrHolder, 21, i, arrHolder(i, 21).Value, 26)

        '  arrHolder(I, 18) = .Cells(I + 1, 24) - Application.SumIf(.Range(.Cells(1, 21), .Cells(I + 1, 21)), _
            .Cells(I + 1, 21).Value, .Range(.Cells(1, 26), .Cells(I + 1, 26)))
End If

            velocityRow = velocityLookup.Item(conUD)
            tempLookup = wsVelocity.Cells(velocityRow, 13)


        'If .Cells(i, 26) > tempLookup Then
        '    .Cells(i, 28).Value = tempLookup
        'Else
        '    .Cells(i, 28).Value = .Cells(i, 26).Value
        'End If
        If arrHolder(i, 26) > tempLookup Then
            arrHolder(i, 28) = tempLookup
        Else
            arrHolder(i, 28) = arrHolder(i, 26)
        End If

        'If .Cells(i, 18).Value < 0 Then
        '    .Cells(i, 29).Value = "C"
        '    .Cells(i, 27).Value = ""
        'Else
        '    .Cells(i, 27) = .Cells(i, 28)
        'End If
        If arrHolder(i, 18) < 0 Then
            arrHolder(i, 29) = "C"
            arrHolder(i, 27) = vbNullString
        Else
            arrHolder(i, 27) = arrHolder(i, 28)
        End If

        '.Cells(i, 31).Value = Application.SumIf(.Range(.Cells(2, 1), .Cells(lrMain, 1)), _
        '    .Cells(i, 1).Value, .Range(.Cells(2, 27), .Cells(lrMain, 27)))
        ' Another SumIf. Same as before, we will have to figure this out separately.
        arrHolder(i, 31) = SumIf(arrHolder(), 1, i, arrHolder(i, 1).Value, 27)

        'arrHolder(I, 31) = Application.SumIf(.Range(.Cells(2, 1), .Cells(lrMain, 1)), _
            .Cells(I + 1, 1).Value, .Range(.Cells(2, 27), .Cells(lrMain, 27)))

        'If .Cells(i, 5) = "" Then
        '    .Cells(i, 35) = ""
        'Else
        '    .Cells(i, 35).Value = Application.Index(indexVelocity1, _
        '    Application.Match(.Cells(i, 5), matchVelocity1, False))
        'End If
        ' Thinking about it now, I am not sure about Application Index/Match on an array either.
        If arrHolder(i, 5) = vbNullString Then
            arrHolder(i, 35) = vbNullString
        Else
            arrHolder(i, 35) = Application.Index(indexVelocity1, _
            Application.Match(arrHolder(i, 5), matchVelocity1, False))
        End If

        'If .Cells(i, 6).Value = 0 Then
        '    .Cells(i, 44).Value = 0
        'Else
        '    .Cells(i, 44).Value = Round(((((.Cells(i, 6).Value / .Cells(i, 11).Value) _
        '        / .Cells(i, 10).Value) - .Cells(i, 8).Value) / .Cells(i, 35).Value), 0.1)
        'End If
        If arrHolder(i, 6) = 0 Then
            arrHolder(i, 44) = 0
        Else
            arrHolder(i, 44) = Round(((((arrHolder(i, 6) / arrHolder(i, 11)) _
                / arrHolder(i, 10)) - arrHolder(i, 8)) / arrHolder(i, 35)), 0.1)
        End If

        'If .Cells(i, 6).Value = 0 Then
        '    .Cells(i, 34).Value = 0
        '    .Cells(i, 33) = 0
        'Else
        '    .Cells(i, 34).Value = Round(((((.Cells(i, 6) / .Cells(i, 11)) / _
        '    .Cells(i, 10)) - .Cells(i, 8)) / .Cells(i, 35)) * .Cells(i, 11), 0.1)
        '    If .Cells(i, 34) > 0 Then
        '        .Cells(i, 33) = .Cells(i, 34)
        '    Else
        '        .Cells(i, 33) = 0
        '    End If
        'End If
        If arrHolder(i, 6) = 0 Then
            arrHolder(i, 34) = 0
            arrHolder(i, 33) = 0
        Else
            arrHolder(i, 34) = Round(((((arrHolder(i, 6) / arrHolder(i, 11)) / _
            arrHolder(i, 10)) - arrHolder(i, 8)) / arrHolder(i, 35)) * arrHolder(i, 11), 0.1)
            If arrHolder(i, 34) > 0 Then
                arrHolder(i, 33) = arrHolder(i, 34)
            Else
                arrHolder(i, 33) = 0
            End If
        End If


        '.Cells(i, 37) = 1 + calcWeek
        arrHolder(i, 37) = 1 + calcWeek

        '.Cells(i, 38) = .Cells(i, 5) & .Cells(i, 37)
        arrHolder(i, 38) = arrHolder(i, 5) & arrHolder(i, 37)

        '.Cells(i, 39).Value = Application.Index(indexVelocity2, _
        '    Application.Match(.Cells(i, 38), matchVelocity2, False))
        arrHolder(i, 39) = Application.Index(indexVelocity2, _
            Application.Match(arrHolder(i, 38), matchVelocity2, False))

        '.Cells(i, 40) = Round(((((.Cells(i, 6) / .Cells(i, 11)) * .Cells(i, 39)) _
        '    - .Cells(i, 6)) - (.Cells(i, 8) - .Cells(i, 6))) / .Cells(i, 35), 0.1)
        arrHolder(i, 40) = Round(((((arrHolder(i, 6) / arrHolder(i, 11)) * arrHolder(i, 39)) _
            - arrHolder(i, 6)) - (arrHolder(i, 8) - arrHolder(i, 6))) / arrHolder(i, 35), 0.1)


        'If .Cells(i, 40) < 0 Then
        '    .Cells(i, 41) = 0
        'Else
        '    .Cells(i, 41) = .Cells(i, 40)
        'End If
        If arrHolder(i, 40) < 0 Then
            arrHolder(i, 41) = 0
        Else
           arrHolder(i, 41) = arrHolder(i, 40)
        End If

        '.Cells(i, 42) = .Cells(i, 41) - .Cells(i, 33)
        arrHolder(i, 42) = arrHolder(i, 41) - arrHolder(i, 33)

        'If .Cells(i, 11) < .Cells(1, 44) Then
        '    .Cells(i, 45) = 0
        '    .Cells(i, 32) = .Cells(i, 45)
        'Else
        '    .Cells(i, 32) = Application.Max(.Cells(i, 33), .Cells(i, 41))
        '    If .Cells(i, 44) < 0 Then
        '        .Cells(i, 45) = ""
        '    Else
        '        .Cells(i, 45) = .Cells(i, 44)
        '    End If
        'End If
        ' Not 100% sure if applicaiton.max will work here.
        If arrHolder(i, 11) < arrHolder(1, 44) Then
            arrHolder(i, 45) = 0
            arrHolder(i, 32) = arrHolder(i, 45)
        Else
            If arrHolder(i, 33) > arrHolder(i, 41) Then
                arrHolder(i, 32) = arrHolder(i, 33)
            Else
                arrHolder(i, 32) = arrHolder(i, 41)
            End If

            If arrHolder(i, 44) < 0 Then
                arrHolder(i, 45) = vbNullString
            Else
                arrHolder(i, 45) = arrHolder(i, 44)
            End If
        End If


        'If .Cells(i, 31) < ShipMin Then
        '    .Cells(i, 47) = 0
        'Else
        '    .Cells(i, 47) = .Cells(i, 27)
        'End If
        If arrHolder(i, 31) < ShipMin Then
            arrHolder(i, 47) = 0
        Else
            arrHolder(i, 47) = arrHolder(i, 27)
        End If

        '.Cells(i, 46) = .Cells(i, 1) & .Cells(i, 22) & .Cells(i, 47)
        arrHolder(i, 46) = arrHolder(i, 1) & arrHolder(i, 22) & arrHolder(i, 47)



    If (i Mod 100) = 0 Then
        Debug.Print "Got to row "; i; " in "; Timer - MainTimer; " seconds."
    End If
End If
Next i

wsMain.Range(wsMain.Cells(2, 1), wsMain.Cells(lrMain, 47)).Value = arrHolder

Erase arrHolder

End Sub

@JohnColeman Without it it throws a "ByRef argument type mimatch". — Emily Alden
– Emily Alden, Commented Mar 22, 2017 at 21:29

PatricK · Accepted Answer · 2017-03-22 21:46:05Z

3

As per comments on your POST, your UDF indicates a String as an input which errors on arrHolder(i, 18) = arrHolder(i, 24) - SumIf(arrHolder, 21, i, arrHolder(i, 21).Value, 26). You can just put convert to string CStr() around the Variant.

SumIf(arrHolder, 21, i, CStr(arrHolder(i, 21)), 26)

answered Mar 22, 2017 at 21:46

PatricK

6,4231 gold badge23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Object Required Error , Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related