How do you access cell's contents via a variable containing cell address??

[gpsmith]

How can you access a cell's value via a vb variable that contains the cell's address.

I have a vb variable named cellAddress (string) that contains "$A$1" and I want to assign the contents of what cellAddress points to another VB variable - how do i Do this?

e.g. if cellAddress = "$a$1" and A1 contains "xyz", I would like to assigne "xyz" to a new vaiable by referencing just cellAddress???


Also - Can anyone reccomend a good Excel VB book? A book that maybe stresses the VB language rather then a cook-book approach. I work in an area where I can't reference the internet for help unless I go to another building so i need a good book.

Thanks,
George

[Leith Ross]

Hello George,

Use your variable that contains "$A$1" as the argument in a Range. The problem here is the range will always refer to the active sheet unless you qualify the Range reference with the worksheet.

  Dim Addx As String
  Dim NewData As Variant

    Addx = "$A$1"
   'This will always retrieve the contents of the ActiveSheet cell "$A$1"
    NewData = Range(Addx)

   'This will always retrieve the contents of Sheet3 cell "$A$1"
    NewData = Worksheets("Sheet3").Range(Addx)

[gpsmith]

Leith - Thats' it, thanks,

George