Having a Named range Name in a Variable

[thesimile]

Hi Gurus,
I'm looking to check if there is a way we can have an excel variable contain the string value of a named range. For Ex
Defined Name "NameRange" points to cell C1 in excel.
I'm looking for a VBA code like

Dim L_Var As String
L_Var = "NameRange"
Msgbox [L_Var].Value.

I would have option to change L_Var dynamically .

 

[Joe4]

You don't need a VBA variable. You can just do:

MsgBox Range("NameRange").Value

Of course, you could set it equal to a variable, i.e.

Dim L_Var as String
L_Var = Range("NameRange").Value
MsgBox L_Var

Note that this will only work for named ranges that are exactly one cell. If a named range contains more than one cell, you will get errors.

 

[thesimile]

Hi Joe,

L_Var = Range("NameRange").Value

will return the value in the cell named "NameRange". What I am trying is to, get the value of the value of the named range. The value of named range is dynamically from a variable

 

[Joe4]

Are you saying that your named range contains the cell address of the cell you really want the value from?
For example, in the named range "NameRange", it has a cell value of C1, and C1 is really the cell you want the value from?

If not, then I have no idea what you are talking about, and it would be best for your to fully walk us through an actual examples of that is in the cells, and what you want.

 

[thesimile]

I was able to accomplish this by the below code

Qst_Range ="Region1"
Set Qst_Name = Range(Names(Qst_Range))

By this , I will be able to change Named range dynamically based on Cell value