Use string cell address from a Excel cell VBA

[ojhawkins]

Hi all,

I have a cell that has the string of a cell address in it say Cell A1 has the string B2

How can I in VBA look at this cell and use the text inside as a range the same as the INDRIECT function.

Also can the cell I am looking at for the string be set as a Dim so if the contents change the new string cell address will be used?

Cheers,
O

 

[repairman615]

Hello,

How about:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> a()<br><SPAN style="color:#00007F">Dim</SPAN> rngAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>rngAddress = Range("A1").Value<br><br>Range(rngAddress).Select<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Peter_SSs]

See if this is the sort of thing you mean.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> UseCellValueAsAddress()<br>    <SPAN style="color:#00007F">Dim</SPAN> Addr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    Addr = Range("A1").Value<br>    MsgBox "The value in cell " & Addr & " is: " & Range(Addr).Value<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[ojhawkins]

Hello,

How about:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> a()<br><SPAN style="color:#00007F">Dim</SPAN> rngAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>rngAddress = Range("A1").Value<br><br>Range(rngAddress).Select<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Perfect thank you!!!!!

I swear I tried this, obviously not.

Cheers,
O