Basically I have a cell named "Cell" for convenience. Now that cell
contains a value saying "A1" but this value can change but always
refers to another cell. I want to get the referring cell as a range.
Currently I have:

Dim cell As Range
cell = Range(Range("Cell").Value)

but this doesn't work for some reason.

Can someone please emlighten me?

Thanks,
Mark

Mark,

cell is a range object, so you need to use Set:

Set cell = Range(Range("Cell").Value)

Then you can do whatever....

msgbox cell.Address & " is now " & cell.Value

But, in general, it is a bad idea to re-use variable and range names. Better would be

Dim myCell As Range
Set MyCell = Range(Range("Cell").Value)

That way, Excel you won't confuse "Cell" and cell

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
Basically I have a cell named "Cell" for convenience. Now that cell
contains a value saying "A1" but this value can change but always
refers to another cell. I want to get the referring cell as a range.
Currently I have:

Dim cell As Range
cell = Range(Range("Cell").Value)

but this doesn't work for some reason.

Can someone please emlighten me?

Thanks,
Mark

It is even a better idea to not use terms like Range, Cell, Sheet or
Worksheet for names of cells, sheets or workbooks at all and to always modify
them with extra characters when using them as variables.

" wrote:

Basically I have a cell named "Cell" for convenience. Now that cell
contains a value saying "A1" but this value can change but always
refers to another cell. I want to get the referring cell as a range.
Currently I have:

Dim cell As Range
cell = Range(Range("Cell").Value)

but this doesn't work for some reason.

Can someone please emlighten me?

Thanks,
Mark