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

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.

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

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

Collectives™ on Stack Overflow

Use a VBA loop to select a series of variables

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related