EXCEL-VBA Change a range with a loop

Question

Set rng = Worksheets("Sheet1").Range("B2")

I have the above and would like to run a loop that changes the range I use/evaluate. I have tried variations of the following.

for x = 2 to 10
Set rng = Worksheets("No-Funding").Range(x & "2")
Next X

I have done some investigating and found this other Stack Overflow: Excel VBA Looping formula to change Range I can not make sense of it in this situation though.

My code will not work if it uses cells either, I have tried that and can only make it work with Range. Thanks for any help provided!

So, using Cells() in your Range() doesn't work? So, what happens when you try something like Set rng = worksheets("No-Funding").Range(cells(1,1),Cells(x,2)) (What range are you trying to get? Right now, your rng will equate to 22, 32. Is that supposed to be a column, or row? — BruceWayne
– BruceWayne, Commented Jul 28, 2015 at 14:15

user4039065 · Accepted Answer · 2015-07-28 14:12:19Z

4

For a strictly numerical increment try,

Set rng = Worksheets("No-Funding").Cells(x, 2)

An xlA1 style reference can be achieved by factoring in the ASCII character.

Set rng = Worksheets("No-Funding").Range(Chr(64 + x) & 2)

answered Jul 28, 2015 at 14:12

user4039065

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

1 Comment

Alexis Moreno Over a year ago

Thank you sir. This is simplicity effective and genius. I'll mark as the answer as soon as the timer passes by

mielk · Accepted Answer · 2015-07-28 14:15:08Z

1

Try this:

For x = 2 To 10
    Set rng = Worksheets("No-Funding").Cells(2, x)
Next x

As far as I know it is no matter for VBA if you use function .Range or .Cells, since both of them return object or Range type.

answered Jul 28, 2015 at 14:15

mielk

3,94014 silver badges20 bronze badges

Comments

John Alexiou · Accepted Answer · 2015-07-28 14:15:16Z

1

Use

Set rng = Worksheets("Sheet1").Range("B2").Offset(0,x)

to move to a different column. Better yet

Dim values as Variant, rng as Range
Set rng = Worksheets("Sheet1").Range("B2").Resize(10,1)
values = rng.Values2

For i=1 to 10
   values(i,1) = 2*values(i,1)
Next i

rng.Values2 = values

answered Jul 28, 2015 at 14:15

John Alexiou

29.4k12 gold badges83 silver badges136 bronze badges

Collectives™ on Stack Overflow

EXCEL-VBA Change a range with a loop

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related