set a variable equal to a value in a named cell

**barrfly** · 10-26-2009, 02:16 PM

I named a cell in a worksheet using the Inset->name->define tool. I can always back track to it using the drop-down arrow in the upper left hand of the worksheet and I know the code in VBA to activate that cell as well.

Application.Goto Reference:="namedcell001"

My question is - how do I set a variable = to the value in that named cell without activating that cell?

similar to var1 = Sheets("Model").Range("R12")

it would read something like this:
var1 = namedcell001

but the above (and several variations of it) does not work.

**mikerickson** · 10-26-2009, 02:26 PM

Please Login or Register  to view this content.

Note that the sheet name is not needed

**barrfly** · 10-26-2009, 02:33 PM

perfect. simple solution yet elusive (for me today at least). Thanks for the quick response.

**Un-Do Re-Do** · 11-10-2009, 01:40 AM

Hi

I think I'm trying to do the same thing as the post originator but when I follow the instructions I still can't get it to work. Note I am a beginner with Visual Basic.

I have a named cell called "this" with the following value "AS41005.2". These are in a worksheet called "Sections".

I want to press a button which then looks up the value "AS41005.2" in another worksheet called "Calculation" and then lands on it - just like the FIND function in excel.

I have no problem with installing and using the button, but can't get the programing to work out. A lot of the time on the forums I can't understand if the codes are in full or in snippets.

My full code (which isn't working) is:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Macro1()

Dim x As Variant
x = Range("this").Value
Sheets("Calculation").Select
Application.Goto Reference:=x

End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Any help will be appreciated.

**DonkeyOte** · 11-10-2009, 03:29 AM

Excel_&_Help, Welcome to the Board, however, please note

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Further, please also ensure you encase your VBA in CODE tags per forum requirements.