How to use range object with variable? Example: A1:B3, then A3:B5, then A5:B7

[makiwara]

Shortly: I need the range of A1:B3, then A3:B5 then A5:b7

_______________________________

Parts of my thoughts, but it leads nowhere because I can't make a code from it, these are just parts of it, but I can't combine them in the right way:

So I have 2 variables:

Dim i as integer
dim j as integer

I have two "for" commands which describe the number next to "A" and "B":

for i=1 to 100 step 2
for j=3 to 100 step 2

And the combination:

Range(A&i":"B&j)

And eventually:
random commands

Can you help me how these parts can become a whole? Have a nice day!

 

[Joe4]

Why not just do:

Dim i as Integer
Dim rng as Range

For i = 1 to 99 Step 2
    Set rng = Range("A" & i & ":B" & i+2)
    ...
Next i

 

[Eric W]

Try something like this:

Dim i as integer, MyRange as Range

    For i = 1 to 100 Step 2
        Set MyRange = Range("A1:B3").Offset(i)
    Next i

I'm a little curious why you'd want to have overlapping ranges though.

 

[makiwara]

Thank you!!!!

Yeah, it was a mistake from me I just wanted to know the method because I couldn't find it out. Thanks again

 

[Joe4]

Dim i as integer, MyRange as Range

    For i = 1 to 100 Step 2
        Set MyRange = Range("A1:B3").Offset(i)
    Next i

Eric,
That won't quite work without a minor adjustment. You either need to start i at 0, or offset by "i-1".
Otherwise, your first range is A2:B4, and you increment by 2 from there.

 

[Eric W]

OK, glad you're happy.

One more thought, I probably should have had

For i = 0 to 99 Step 2

Because you want to start at A1:B3, and an offset of 0 is the original cell. If you want an offset of rows, and columns, you'd use

.Offset(r, c)

Edit: Joe4, yup, I belatedly saw that myself. Good eye.

 

[Joe4]

Thank you!!!!

You are welcome!