variable for cell reference

[tahoney]

just found this board and looks like a great place to ask for help. How can I use a variable like x for cell reference example: (bx:sx) were x is a number between 5 and 15? Is this possible? I can't seem to find any answer in the help section of excel. Thanks for any and all help.

 

[just_jon]

just found this board and looks like a great place to ask for help. How can I use a variable like x for cell reference example: (bx:sx) were x is a number between 5 and 15? Is this possible? I can't seem to find any answer in the help section of excel. Thanks for any and all help.

Like this --

Dim x as Integer, Rng as Range
x = 11
Set Rng = Range("A" & x & ":B" & x)

 

[tahoney]

just found this board and looks like a great place to ask for help. How can I use a variable like x for cell reference example: (bx:sx) were x is a number between 5 and 15? Is this possible? I can't seem to find any answer in the help section of excel. Thanks for any and all help.

Like this --

Dim x as Integer, Rng as Range
x = 11
Set Rng = Range("A" & x & ":B" & x)

just_jon thats is just what i needed thanks very much

 

[just_jon]

Perhaps you were looking for --

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Cells(Rows.Count, 1).End(xlUp).Row
    Cells(i, "a") = Cells(i, "a") + i
    Cells(i, 2) = Cells(i, 2) / i
<SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>