Cell address to another cell in a named range

[most]

I would to get a cell address from a named range which contains the address in text format. Searched but only get none related hits, any suggestion how I could solve this?

Sheet ONE cell A1 = "Sheets("TWO").Range("B2")"
Sheet TWO cell B2 = 1000

ANamedRange =ONE!$A$1 (Name manager)

debug.print ANamedRange.value

Expected result: 1000

Why? I want to make a sheet with "parameters" with reference to correct cells needed for my code, so if the layout change I only need to change the parameter sheet.

 

[Georgiboy]

Hmm can't think f a way of doing it without pulling the value in A1 apart, see below:

Sub ReturnAddress()
    Dim str As String, rng As Range
    Dim sSht As String, sRng As String

    str = Range("ANamedRange").Value ' value from named range
    sSht = Split(Split(str, ".")(0), """")(1) ' get sheet part
    sRng = Split(Split(str, ".")(1), """")(1) ' get address part
    Set rng = Sheets(sSht).Range(sRng) ' create a range from the above created strings
    
    Debug.Print rng.Value ' ask for rng value
End Sub

 

[Georgiboy]

Another similar option:

Sub ReturnAddress()
    Dim str As String
    Dim sSht As String, sRng As String

    str = Range("ANamedRange").Value ' value from named range
    sSht = Split(Split(str, ".")(0), """")(1) ' get sheet part
    sRng = Split(Split(str, ".")(1), """")(1) ' get address part
   
    Debug.Print Evaluate("'" & sSht & "'!" & sRng)
End Sub

 

[FormR]

Hi, why wouldn't you just assign the actual range you want to the named range rather than to a cell that contains the address of the range you want, which seems a tad convoluted.