Assigning a range variable to row of current cell

Question

I am having difficulty assigning the range of the active cell's row to a ranged variable. When I attempt to copy this variable in a range function call, I get the "Method range of object global failed" error. Any tips?

Sub GroupLanes()
Dim Origin, Destination, ODPair As String
Dim mainWorkbook As Workbook
Dim Index As Long
Dim IB, OB As Range

Set mainWorkbook = ActiveWorkbook
Index = 6

Range("G" & Index).Select
Origin = ActiveCell.Value

Debug.Print Origin

Range("H" & Index).Select
Destination = ActiveCell.Value

Debug.Print Destination

Range("AV" & Index).Select
ODPair = ActiveCell.Value

Debug.Print ODPair

Do Until IsEmpty(ActiveCell)

    Set IB = ActiveCell.EntireRow

    Range("AV6").Select
    ODPair = ActiveCell.Value

    Do Until IsEmpty(ActiveCell)

        If (Mid(ODPair, 1, 5) = Destination And Mid(ODPair, 6, 5) = Origin) Then
            Set OB = ActiveCell.EntireRow

            Range(IB).Copy
            Sheets("Sheet2").Range("A" & Index & ":" & "AV" & Index).PasteSpecial xlPasteValues

            Range(OB).Copy
            Sheets("Sheet2").Range("A" & Index & ":" & "AV" & Index).PasteSpecial xlPasteValues

            ActiveCell.Offset(1, 0).Select
            ODPair = ActiveCell.Value
        Else
            ActiveCell.Offset(1, 0).Select
            ODPair = ActiveCell.Value
            Debug.Print ODPair
        End If

    Loop

    Index = Index + 1

    Range("G" & Index).Select
    Origin = ActiveCell.Value

    Range("H" & Index).Select
    Destination = ActiveCell.Value

LoopEnd Sub

Too long code, I can't be bothered to read through it all. Which row produces the error? Also, read [this](http://stackoverflow.com/help/mcve) — vacip, Apr 20 '16 at 20:28
First things first; get rid of the `.Select` and `.Activate` It slows down the code. For a great tutorial and explanation look [HERE](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Scott Craner, Apr 20 '16 at 20:57
I'm not really worried about scalability. This is an ad-hoc request that involves a very limited amount of data. — Eric Hondzinski, Apr 21 '16 at 01:55

score 1 · Accepted Answer · edited May 08 '18 at 21:49

Change:

Range(IB).Copy

to

IB.Copy

IB is already a Range (kind of, see below) so when you call it in a range function, it's looking for a range with a name that's equal to what's in IB on your sheet.

In addition to Scott's tips about Select and Activate, be aware that

Dim IB, OB As Range

declares OB as a range, just like you want, but declares IB as a Variant. For more info, see this Chip Pearson page and scroll down to "Pay Attention To Variables Declared With One Dim Statement."

Assigning a range variable to row of current cell

1 Answers1