Use a VBA loop to select a series of variables

Question

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?

are you trying to transpose a row to a column, or column to row? — paul bica
– paul bica, Commented Aug 6, 2015 at 3:18

user4039065 · Accepted Answer · 2015-08-06 03:25:38Z

1

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.

answered Aug 6, 2015 at 3:25

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

Phil Over a year ago

thanks Jeeped, but this is selecting range("R(i+1)") instead of achieving r1.Copy, r2.Copy, and so on.

Tmdean · Accepted Answer · 2015-08-06 03:36:05Z

0

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

edited Aug 6, 2015 at 3:36

answered Aug 6, 2015 at 3:22

Tmdean

9,31945 silver badges51 bronze badges

Collectives™ on Stack Overflow

Use a VBA loop to select a series of variables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related