PDA

View Full Version : [SOLVED] How to Construct a Column Variable for Range



Cyberdude
09-28-2005, 02:10 PM
I have a series of variables (Col1, Col2, ...) for column letters. If I substitute a variable for the column letter:
Range(Col1 & 3).Select
it works just fine.
I would like to create a loop that looks something like:


Dim Col1 As String, Col2 As String
Col1 = "A"
Col2 = "F"
For N=1 To 2
Range("Col" & N & N + 2).Clear
Next N

This of course doesn't work because it would create something like:

Range(Col12).Clear
In fact it might create something like:
Range("Col"12).Clear (I'm not sure)
What I need is something like the Excel "Indirect" function, I think.

Range(Indirect("Col" & N) & N + 2).Clear
In any case I'm going crazy trying to figure it out. :banghead:
Any ideas?

Bob Phillips
09-28-2005, 06:44 PM
I have a series of variables (Col1, Col2, ...) for column letters. If I substitute a variable for the column letter:

Range(Col1 & 3).Select
it works just fine.
I would like to create a loop that looks something like:


Dim Col1 As String, Col2 As String
Col1 = "A"
Col2 = "F"
For N=1 To 2
Range("Col" & N & N + 2).Clear
Next N

This of course doesn't work because it would create something like:

Range(Col12).Clear
In fact it might create something like:
Range("Col"12).Clear (I'm not sure)
What I need is something like the Excel "Indirect" function, I think.

Range(Indirect("Col" & N) & N + 2).Clear
In any case I'm going crazy trying to figure it out. :banghead:
Any ideas?

Dude,

Try an array



Dim ColArray
Dim N As Long
ColArray = Array("A", "F")
For N = 0 To 1
Range(ColArray(N), N + 3).Clear
Next N

Cyberdude
09-28-2005, 08:24 PM
Hey, xld, that looks promising ... I'll give it a try. Thanx.

Bob Phillips
09-28-2005, 10:24 PM
Hey, xld, that looks promising ... I'll give it a try. Thanx.
If you do, get it right (as I didn't)



ColArray = Array("A", "F")
For N = 0 To 1
Range(ColArray(N) & N + 3).Clear
Next N

Cyberdude
09-29-2005, 12:04 PM
Hey, xld, the array solution works (and I did spot the missing "&"). That's a BIG help in reducing my code lines.
Thanx. :beerchug: