Basic variable question 2

[yorrick]

apologies for this question in advance!

I have a macro that has a loop within it. I want the loop to run through several times, and each time I want a new starting point within the spreadsheet.

I have been using Range("C2") as the starting point.
but for the next iteration it needs to be range("c45"

so I just want to put range("X") at the top of the loop where I can manipulate X.
X = "C2"
X = "C45"

etc

I have no VBA skill obviously, but in laymans terms, what should I be doing???

 

[JerryKlmns]

And the loop is????

 

[PHV]

You may use something like this:
For X In Array("C2", "C45", ...)
' do stuff with Range(X)
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

option base 1
Sub ExampleLoops()
dim StartArr as variant, StartRng as range, i as integer

StartArr = array("C2","C45","C78")

For i = 1 to 3
  StartRng = Range(StartArr(i))
    'Other loop starts here, using StartRng
    'do stuff
    'End Inner loop
Next i
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[yorrick]

Thanks xlbo,

Unfortunately this brings back a Run time error #9. Subscript out of range.

Also how do I put the cursor on startrng?

 

[xlbo]

well there is no way what I showed you will work - it was an example - you would need to put another loop in there.

If code isn't working, please post what you have actually written and indicate the line that is highlighted as giving the error

please explain why you want to put the cursor on StartRng....you have not actually told us what you are trying to achieve so it is difficult to help you

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question