CODEWITHSUNDEEP
vbaexcel
D. W.
D. W.

Reputation: 163

Excel VBA: Selecting Different Ranges in Loop

I'm trying to select two different ranges in a loop (i.e. first iteration selects one range, second iteration selects another range). Here's my try at just seeing if I could do a simple task like selecting two ranges one after another:

Sub SelectingTwoRanges()
Dim i As Integer
Dim m As Integer
Dim n As Integer
For i = 1 To 2
    m = i * 50 - 48
    n = i * 50 + 1
    Range(Cells(m, 1), Cells(n, 2)).Select
Next i

End Sub

This gives the error: "Method 'Cells' of Object '_Global' Failed"

I've tried it before as:

Sub SelectingTwoRanges()
Dim i As Integer
Dim m As Integer
Dim n As Integer
For i = 1 To 2
    m = i * 50 - 48
    n = i * 50 + 1
    Range("Am:Bn").Select
Next i

End Sub

Or:

Sub SelectingTwoRanges()
Dim i As Integer
Dim m As Integer
Dim n As Integer
For i = 1 To 2
    m = i * 50 - 48
    n = i * 50 + 1
    Range("A$n:B:m").Select
Next i

End Sub

And none of the above worked. I think my problem is using loop-dependent variable inside strings (which I tried to avoid by using the "Cells(#, #)..." formation inside the "Range()". But now I'm just stuck.

Is this possible?

Upvotes: 1

Views: 26222

Answers (2)

Alter
Alter

Reputation: 3464

It's likely you're getting the "Method 'Cells' of Object '_Global' Failed" error because your's Cells() method isn't attached to an object. According to this post (Method 'Range' of object '_Global' failed. error), you can avoid that by using "activeSheet".

Sub SelectingTwoRanges2()
    Dim i As Integer
    Dim m As Integer
    Dim n As Integer
    For i = 1 To 2
        m = i * 50 - 48
        n = i * 50 + 1
        With ActiveSheet
            .Range(.Cells(m, 1), .Cells(n, 2)).Select
        End With
    Next i
End Sub

As for your other examples; you're completely right. Including a variable name in a string doesn't reference the variable at all. You can use the following format to concatenate strings (VBA will automatically try to convert variable values to strings in concatenation)

Range("A" & m & ":B" & n).Select

Personally I like using CStr() to make sure VBA converts a value to a string

Range("A" & CStr(m) & ":B" & CStr(n)).Select

Upvotes: 2

asp8811
asp8811

Reputation: 803

The first example worked for me, but try:

 activesheet.Range(activesheet.Cells(m, 1), activesheet.Cells(n, 2)).Select

This will fully qualify the Cells() range reference.

Upvotes: 1

Related Questions