Error in selecting range to copy

Question

I need to select a specific range to copy with variable start and end point at each for loop, so used the Range(Cells(x,x), Cells(y,y).Select method. This would give me an error:

For i = 1 To 40
        Worksheets("BloombergData").Select
        Worksheets("BloombergData").Range(Cells(5, 2 + 11 * (i - 1)), Cells(4 + Worksheets("Lookup").Cells(i + 1, 3).Value, 11 + 11 * (i - 1))).Select
        Selection.Copy
        Worksheets("Data_Rearranged").Range(Cells(6 + Worksheets("Lookup").Cells(i, 3).Value, 4), Cells(5 + Worksheets("Lookup").Cells(i + 1, 3).Value, Data_Columns + 3)).Select
        Selection.Paste
    Next i

Even when I simplified what goes into the cells function, it still didn't work. I tried this to test and it gave me the same error:

Worksheets("BloombergData").Range(Cells(1, 1), Cells(2, 1)).Select
Selection.Copy

It only worked when I put the actual cell in the Range, i.e. Range("A1")

But my range is variable so I need to find some way of selecting a variable range.

Help please?

Answer 1

It is but obvious that you will get the error. Your Cells Object is not fully Qualified. Notice the DOT before the Cells Object in the code below.

BTW I have not tested what 2 + 11 * (i - 1) evaluates to. So if evaluates to an unacceptable number then the code will fail again.

Sub Sample()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("BloombergData")

    With ws
        For i = 1 To 40
            .Range(.Cells(5, 2 + 11 * (i - 1)), .Cells(4 + Worksheets("Lookup").Cells(i + 1, 3).Value, 11 + 11 * (i - 1))).Copy
            '
            '~~> Rest of the code
            '
        Next
    End With
End Sub

INTERESTING READ

For easy understanding you could also break down your code to this. It will become easy to debug it later :)

Sub Sample()
    Dim ws As Worksheet
    Dim r1 As Long, c1 As Long
    Dim r2 As Long, c2 As Long

    Set ws = ThisWorkbook.Worksheets("BloombergData")

    With ws
        For i = 1 To 40
            r1 = 5
            c1 = 2 + 11 * (i - 1)

            r2 = 4 + Worksheets("Lookup").Cells(i + 1, 3).Value
            c2 = 11 + 11 * (i - 1)

            .Range(.Cells(r1, c1), .Cells(r2, c2)).Copy

            '
            '~~> Rest of the code
            '
        Next
    End With
End Sub