[RESOLVED] (Excel VBA) Run-time error 424: Object required for a collection

[mfreem02]

Hi all, first post so forgive me if I get this wrong. I keep getting a run-time error 424 when trying to debug.print a previously defined collection and I am not sure why. Following is the current code.

Sub QuoteArr()

    Dim sh As Worksheet, sh2 As Worksheet
    Dim arrQuote() As String, arrParent() As String, arrOffer() As String, arrPrdMdl() As String
    Dim lLastRow As Integer, lLstRw2 As Integer
    Dim collQuote As New Collection
    Dim c As Range, rng As Range
    Dim i As Integer, j As Integer, k As Integer
        
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    Set sh2 = ThisWorkbook.Worksheets("Sheet2")
    Set rng = sh2.Range("a1").CurrentRegion

' Get last row with text by searching from the bottom of the worksheet
    lLastRow = sh.Cells(sh.Rows.count, 1).End(xlUp).Row
    lLstRw2 = sh2.Cells(sh2.Rows.count, 1).End(xlUp).Row
' Print the row number
    Debug.Print lLastRow
    Debug.Print lLstRw2

With sh2
    For Each c In rng
        collQuote.Add c.Value
    Next
'    Debug.Print collQuote.count
'    For j = 1 To collQuote.count
'        Debug.Print collQuote(j)
'    Next j
End With
'    Debug.Print collQuote.count
'    For j = 1 To collQuote.count
'        Debug.Print collQuote(j)
'    Next j
With sh
ReDim arrQuote(1 To lLastRow), arrParent(1 To lLastRow), arrOffer(1 To lLastRow), arrPrdMdl(1 To lLastRow)
    For i = 1 To lLastRow
        arrQuote(i) = .Cells(i, 1)
        arrPrdMdl(i) = .Cells(i, 9)
        arrParent(i) = .Cells(i, 20)
        arrOffer(i) = .Cells(i, 21)
    Next i
'    Debug.Print "Quote"
'    For i = LBound(arrQuote) To UBound(arrQuote)
'        Debug.Print arrQuote(i)
'    Next i
End With

'to access the collection
    For k = 2 To collQuote.count
        Debug.Print collQuote(k).Name 'this is where the error occurs
    Next k
    
End Sub

Attached is the workbook and thanks for taking a look at this.

[TnTinMN]

    For k = 2 To collQuote.count
        Debug.Print collQuote(k).Name 'this is where the error occurs
    Next k

You populated the collection like this:

    For Each c In rng
        collQuote.Add c.Value
    Next

So each item in the collection is a value (number, string, null). Those values to not have a Property named "Name", so the best you can do is print the value out.

   For k = 2 To collQuote.count
        Debug.Print collQuote(k)
    Next k

[mfreem02]

Thank you TnTimMN!! That fixed it. And if I had been paying enough attention to what I actually wrote, I would have seen that I did the exact same print operation previously without the .Name which worked without erroring out. Sometimes it just takes a different pair of eyes!