VBA Runtime Error 1004 "Application-defined or Object-defined error" when trying to assign-CodePudding

I am not sure why I am getting this error when trying to run the code below

Sub introtocollection()
    Dim i As Integer
    Dim coll As New Collection
    Dim r As Range
    Set r = Worksheets("Sheet1").Range(Cells(1, 1), Cells(5, 2))
    
    
    For i = 2 To 5
        coll.Add r.Cells(i, 2)
        
    Next i
    
    For i = 2 To 5
       Worksheets("Sheet1").Range(Cells(i, 6)).Value = coll(i).Value
       
        
    Next i
    
End Sub

The error is highlighted in this code line below

Worksheets("Sheet1").Range(Cells(i, 6)).Value = coll(i).Value

CodePudding user response：

try Worksheets("Sheet1").Range(Cells(i, 6)).Value = coll(i,2).Value it may help

CodePudding user response：

This error most likely arose due to the fact that the code is trying to reach some area indirectly by the address or name in Cells(i,6) (see how the first parameter is interpreted in Range). Suppose you put a string "A1" into the cell F2 which is Cells(2, 6). Then the code Range(Cells(2,6)) will substitute Cells(2,6) by its value and return Range("A1").

I assume that this wasn't your intent. I guess that the Cells(i,6) are empty for i=2 to 5 or do not contain correct range addresses. Maybe you wanted to do something like this:

Worksheets("Sheet1").Cells(i, 6).Value = coll(i).Value

If so, then pay attention to how the assignment of the Collection keys is provided, because you have another trap on your way. If you omit declaring keys explicitly, then they are assigned automatically starting from 1. So the next error you'll get is Subscript out of range in the second for-loop at i=5. To avoid this, declare the keys explicitly:

coll.Add r.Cells(i, 2), Key:=i

P.S.
If, nevertheless, you wanted to do exactly indirect access to cells, then you should check the correctness of addresses in the F2:F5 range. I can't imagine that anything else could've caused the error you mentioned.