CODEWITHSUNDEEP
vbaexcel
Phil
Phil

Reputation: 95

Use a VBA loop to select a series of variables

I have a series of variables (each declared as a range) in a VBA script as follows:

r1 = range
r2 = range
...
r100 = range

Ideally I'd like to use a for loop to select, copy, and paste (transpose) each range in succession. I'm writing my code via trial and error, and I have little familiarity with VBA. Currently I'm using a loop like the following:

For i = 0 To 99 Step 1
    Dim Num As Integer
    Num = i + 1
    Num = CStr(Num)
    Dim R As Range
    R = "r" & Num
    R.Select
    Selection.Copy
    Range("TARGET RANGE").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
Next i

Can anyone help me debug this loop and/or find the best way to achieve this?

Upvotes: 0

Views: 476

Answers (2)

Tmdean
Tmdean

Reputation: 9299

You can use a For Each ... In loop for this.

Dim Bag As New Collection
Dim Target As Range
Dim r As Range

Bag.Add [A1:A50]
Bag.Add [C3:F100]
Bag.Add [Sheet2!H1:L1]
Bag.Add ['[Another file.xlsx]Sheet1'!A1:B100]

Set Target = [Output!A1]

For Each r In Bag
    ' Size target to be the same dimensions as r transposed
    Set Target = Target.Resize(r.Columns.Count, r.Rows.Count)

    ' transpose and copy values
    Target.Value = Application.Transpose(r.Value)

    ' shift target down to next empty space
    Set Target = Target.Offset(Target.Rows.Count)
Next

Upvotes: 0

user4039065
user4039065

Reputation:

You need to Set a Range object.

Dim Num As Integer, R As Range
For i = 0 To 99 Step 1
    Num = i + 1
    SET R = RANGE("r" & Num)
    R.Copy Destination:=Range("TARGET RANGE")
Next i

It is not entirely clear what you intend to accomplish but the above should do what is expected of it.

Upvotes: 1

Related Questions