Method range of object failed - Please help

[whitethorn]

Hi All,


I am trying to define a variant based on the results of another loop and I am currently using the below syntax, however i am getting a method range of object failed. I've been trying for about 3 hours to figure this out and have made some changes but can't seem to see what's going wrong. Any ideas?

For j = 1 To finalrow3
    
    For l = 1 To 200
        If base(j, 1) = firstlast(l, 2) And Year(dates(j, 1)) <= firstlast(l, 1) And Year(dates(j, 2)) >= firstlast(l, 1) Then
            first = firstlast(l, 3)
            last = firstlast(l, 4)
         End If
         
         If first = 0 Then Exit For
            Dim base1(), bil(), mh() As Variant
            
             base1() = Capacity.Range(Cells(first, 8), Cells(last, 8)) ' get method range object failed here 
             bil() = Capacity.Range(Cells(first, 9), Cells(last, 15))
             mh() = Capacity.Range(Cells(first, 23), Cells(last, 24))
        Next l
    Next j

Thank you

 

[Norie]

Try this.

Dim base1(), bil(), mh() As Variant

    For j = 1 To finalrow3

        For l = 1 To 200
            If Base(j, 1) = firstlast(l, 2) And Year(dates(j, 1)) <= firstlast(l, 1) And Year(dates(j, 2)) >= firstlast(l, 1) Then
                first = firstlast(l, 3)
                last = firstlast(l, 4)
            End If

            If first = 0 Then Exit For
            With Capacity
                base1() = .Range(.Cells(first, 8), .Cells(last, 8)) ' get method range object failed here
                bil() = .Range(.Cells(first, 9), .Cells(last, 15))
                mh() = .Range(.Cells(first, 23), .Cells(last, 24))
            End With
        Next l
    Next j

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...cell-entry-earlier-statement.html#post5090790

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).