VBA - select a named range by referring to another cell with the name in it

Question

I have 150 named ranges that I am trying to perform a loop function on. I have the names of the 150 ranges listed in my worksheet. How do I reference the content of the name when I am trying to select the named range using my VBA code?

For example:

Name Range #1: EE_001 refers to Worksheet1!A1:Z100
Name Range #2: EE_002 refers to Worksheet1!A101:Z200

On Worksheet2!A1 the contents are EE_001
On Worksheet2!A2 the contents are EE_002

Assuming I am already on Worksheet2, in VBA I would like to select named range #1 with the code...

Range(Range("A1").value).select

But that doesn't work.

Any help would be greatly appreciated.

Likely you need to qualify at least the inner `Range` call with its parent worksheet. — BigBen, Mar 23 '23 at 20:44
Also for you to select a cell(s) the sheet on which they reside needs to be active. So before you need: `WorkSheets("Worksheet1").Activate` then you can use `Range(WorkSheets("Worksheet2").Range("A1").value).select` But before you do that ask yourself is selecting a range is really what you want to do, See: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Scott Craner, Mar 23 '23 at 21:01
how about `Application.Goto Reference:="EE_001"` - you can replicate it with recording a macro and then `Home->Find&Select->Go to...` — Stachu, Mar 23 '23 at 21:34
Yes, they were supposed to refer to different ranges. I need to reference the cell that contains the name "EE_001". I have 150 names that I am trying to loop through without creating 150 snippets of code for each named range — David Odorizzi, Mar 23 '23 at 21:43

VBasic2008 · Answer 1 · 2023-03-24T04:35:15.340

Loop Named Ranges Using a List in a Column

It is assumed that the names are workbook-scoped.

Sub ProcessNamedRanges()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim sws As Worksheet: Set sws = wb.Sheets("Sheet2")
    Dim srg As Range: Set srg = sws.Range("A1:A150")
    
    Dim drg As Range, sCell As Range
    
    For Each sCell In srg.Cells
        On Error Resume Next
            Set drg = wb.Names(sCell.Value).RefersToRange
        On Error GoTo 0
        If Not drg Is Nothing Then
            ' Perform operations e.g.:
            Debug.Print drg.Worksheet.Name, drg.Address
            
            Set drg = Nothing ' reset for the next iteration
        Else
            ' invalid name or is not a range reference
            ' or is not of workbook scope...:
            Debug.Print "Skipped """ & Cstr(sCell.Value) & """."
        End If
    Next sCell
    
    MsgBox "Named ranges processed.", vbInformation

End Sub

score 0 · Answer 2 · answered Mar 23 '23 at 21:56

You shouldn't use select but here's a way to find the named range if it's called from the active sheet. Better if you specified the sheet ID first.

Sub selectnamedRange()
Dim n As Name, someText As String, tRange As Range

'it would be better if this specified the sheet first such as sheet1.Range("A1")
someText = Range("A1").Value

For Each n In ThisWorkbook.Names
    'multiple conditions to account if in workbook or worksheet...
    If n.Name = someText Or InStr(1, "!" & n.Name, someText, vbTextCompare) Then
        'found the range
        Set tRange = n.RefersToRange
        Exit For
    End If
Next n

    tRange.Worksheet.Activate 'need this to jump sheets
    tRange.Select

End Sub

Elbert Villarreal · Answer 3 · 2023-03-23T22:30:31.850

Here is Sheet AA

Here is Sheet BB

The code:

Option Explicit

Sub Go_Over_Names()
    'Store the Workbook
    Dim Wrk As Workbook: Set Wrk = ThisWorkbook
    'This vars AA and BB to store the sheets
    Dim BB As Worksheet: Set BB = Wrk.Worksheets("BB")
    Dim AA As Worksheet: Set AA = Wrk.Worksheets("AA")
    'Here store the range with the names in each cell in the Sheet BB
    Dim NmsRng As Range: Set NmsRng = BB.Range("A1:A4")
    'To use it in the for loop
    Dim i As Range
    Dim Rng As Range
    'to store the range name and the sheet where the range exists
    Dim NmStr As String
    Dim NmSht As String
    'To store the sheet where the name exists
    Dim Sht As Worksheet
    
    'Let's work... Go the Sheet (BB) where the names (Strings) exist
    BB.Activate
    'Loop over the cells with the 150 names... Just 3 but you know...
    For Each i In NmsRng
        'Store the name range into the var
        NmStr = i.Value
        'Sice the name ranges have the scope for the whole workbook, we can
        'take advantage of that.
        'if not, it won't be any trouble. 
        Set Rng = Range(NmStr)
        'Here we take the name of the sheet where the actual range exist...
        NmSht = Rng.Parent.Name
        Set Sht = Wrk.Worksheets(NmSht)
        'Let's go to that sheet
        Sht.Activate
        'Here you can do whatever you want with your range...
        'select...
        ' As @pgSystemTester say, the Select statement, is not good the most of the times
        'Using the Rng var, you can copy, change value, loop and anything else
       'over the name range.
        Rng.Select
        'Change the value.
        Rng.Value = 1
    Next i
End Sub

Result:

NOTE: The Name Range EE_004 had scope just to sheet AA, so, it won't be any trouble if the range is global (Workbook) or just on the Worksheet. The other ranges are global.

VBA - select a named range by referring to another cell with the name in it

3 Answers3

Loop Named Ranges Using a List in a Column