how do you refer to the text in a cell in VBA?

[Machds42]

I know that when referring to the text in a cell in excel I would write something like

INDIRECT(".....") etc

But if i have a cell with a name in it, and want excel to always copy the text in the cell, how do i write that in vba

e.g

Application.Goto Reference:=xxxxxx

cheers

 

[Andrew Poulsom]

Say you have a range named MyName and A1 contains MyName:

Application.Goto Reference:=Range("A1").Value

will select that named range.

 

[Machds42]

but I want to refer to the text in the cell, not the cell reference

 

[Richard Schollar]

Hi

I'm not sure I appreciate what it is you're asking but say you have a named range named "richard" which refers to cell D1 and you have cell A1 containing the text "richard" then you can return the value of D1 via this structure:

Msgbox Range(Range("A1").Value).Value

 

[Machds42]

I have a named range called "x"

Howver, depending on what i choose from a drop down list, the named range may change to something else, say "y", since i have more than one named range

I need to write a macro which will take me straight to the named range ("x" or "y" depending on which i choose).

Example

I have range A1:A10 named "Bob"
I have range B1:B10 named "Bill"

In C1 I have a drop down list, containing "Bob" and "Bill"

I choose Bob from the drop down list

I know want to write a macro that takes me to Bob in the sheet

So C1 contains the text of the name I need to find

Hence why i thought i might be able to use;

Application.Goto Reference:=then the reference to the text in the cell, as it is always changing

 

[Andrew Poulsom]

Try:

Application.Goto Reference:=Range("C1").Value

 

[Machds42]

legend!!!!!

Done, done and done

thank you so much, you have saved me heaps of time

cheers