Using Named Ranges as Data

[N1Martian]

I have a number of named ranges in my workbook, so I typed all the names into cells to capture as a range unto itself. Now I want to use it do some function. Here is a quick example:

Dim X as string
A = Range("NamedRanges")
X = A(3,1)
Combobox1.RowSource = X

So I have the name of the range I want stored in X as a string, but I want to be able to use that to redefine a new range. I would settle for B = Range(X), but none of it works.

Any help would be great. Thanks in advance!

 

[Ralajer]

This should work if I am reading your requirements correctly.

Sub NamedRangeOfNames()
    Dim rngOfNames As Range, rngName As Range
    Dim mainName As String, subName As String
    
    '// Named range that contain list of names of other named ranges.
    mainName = "namedrange"
    
    '// Assign named range to VBA range variable
    Set rngOfNames = ActiveWorkbook.Names(mainName).RefersToRange
    
    '// Get sub name in list of named ranges
    subName = rngOfNames.Cells(3, 1).Value
    
    '// Assign named range to VBA range variable based on value in subname variable
    Set rngName = ActiveWorkbook.Names(subName).RefersToRange
End Sub

Let me know if this works for you.

 

[N1Martian]

I was just learning about the names object, but it's still not working for me, specifically this line:

Set rngName = ActiveWorkbook.Names(subName).RefersToRange

I do have subName as a working string that matches a named range in my workbook. However I'm getting a 1004 runtime error (at the line "Set XB"). Here is the exact code I'm working with.

Sub test()

Dim x
Dim y As String
Dim XB As Range

XA = Range("Subcats")

For x = 1 To 5
y = XA(1, x)
MsgBox
Set XB = ActiveWorkbook.Names.RefersToRange
MsgBox (XB(1, 1))
Next x

End Sub

Heres an example of my spreadsheet:

Range1 Range2 Range3 Range4 Range5 <--Range set as "subcats"
--------------------------------------
1.........2..........3.........4..........5
6.........7..........8.........9.........10

So the first msgbox will return a name like range1, and the second should return the first cell in range1. But I still can't get it to work.

 

[Ralajer]

There doesn't appear to be anything wrong with the code. I was able to reproduce the 1004 Error if the named range stored in 'y' didn't exist or the name refers to a constant.

Open the Name Manager and verify the names match what you have on the sheet or do not refer to constants.

 

[N1Martian]

I am using an dynamic named range using the offset() function. Is that the problem?

Edit: I had the first range in my list defined incorrectly. Now it works. Thanks for the help Rob!