Loop over all the values of the named ranges

Question

I have a number of named ranges within the scope of my Workbook.

Now I want to loop over each named range by following the second answer of this related topic

Sub Range1()

Dim i As Integer

For i = 2 To ActiveWorkbook.Names.Count
    MsgBox ActiveWorkbook.Names(i).Name
Next i

End Sub

This macro shows me all the names such as C_1 in a msgbox. I modify the script in the following way to "call" each of the ranges based on the name of the corresponding range.

Sub Range2()

Dim i As Integer
Dim rng As Range
Dim nm As Name
Dim rng_name As String

For i = 2 To ActiveWorkbook.Names.Count
   Set nm = ActiveWorkbook.Names(i)
   rng_name = nm.Name
   Set rng = Application.Range(nm.Name)           `This line yields an error
   MsgBox rng.Address
Next i

End Sub

The error I get is:

"Run-time error '1004': Method 'Range' of object'_ Apolication failed

Any idea what I am doing wrong? If I inspect the elements I see that rng_name is equal to "C_1".

EDIT

I believe that it has something to do with how the named ranges are created. This is done in the following way:

Sub createRanges()

Dim LastRowAll As Long, LastRowUnique As Long
Dim x, y
Dim rng As Range
Dim rng_name As String

LastRowUnique = Sheets("Lists").Range("J2").End(xlDown).Row
LastRowAll = Sheets("Deribit").Range("D8").End(xlDown).Row

For Each x In Sheets("Lists").Range("J2:J" & LastRowUnique)
    For Each y In Sheets("Deribit").Range("D8:D" & LastRowAll)
        If y.Offset(0, -1).value = "Call" Then
            If rng Is Nothing And y = x Then
                Set rng = y.Offset(0, -2)
            ElseIf y = x Then
                Set rng = Union(rng, y.Offset(0, -2))
            End If
        Else:
        End If
    Next y
    rng_name = "C_" & x.Offset(0, -1).value
    ThisWorkbook.Names.Add Name:=rng_name, RefersTo:=rng.Address
    Set rng = Nothing
Next x

@tigeravatar; if I inspect `nm` I see that it has the following error `Application-defined or object-defined error` — HJA24, Jun 27 '19 at 06:43
@Vityata is it possible that there is something wrong with my defined ranges? — HJA24, Jun 27 '19 at 06:44
@tigeravatar I came back to the same solution as the simplest approach, since the Sheet names being attached to the string `nm` appears to be the issue, though I believe that you could use `strLoc = InStr(nm,"!") // nm = Right(nm,len(nm)-strLoc))` as an alternative. — Cyril, Jun 27 '19 at 13:20

Cyril · Accepted Answer · 2019-06-27T13:16:27.697

See:

For i = 1 To ActiveWorkbook.Names.Count
    Set nm = ActiveWorkbook.Names(i)
    Set rng = ActiveWorkbook.Names(nm.Name) 'This is where the error was
    MsgBox rng.Address
Next i

I also dropped the rng_name, since you didn't use it.

Edit1:

I was unable to get Application.Range(nm.Name) to work originally, but was able to get ActiveWorkbook.Range(nm.Name) to work, using ranges on different sheets named "c" and "d". I noted that nm = ActiveWorkbook.Names(i) did not include the sheet name when it was recorded.

When naming ranges with the underscore and number in them (I tried c_1 & c_2) I was unable to get my code to work; on inspecting nm in debug mode, I noticed that it also listed the sheet name. I had to specify that than rng would .RefersToRange to get the cell address to show up without the sheet name.

I did two additional checks, one with the underscore without number (used "d_") and another with a number and no underscore (used "name2"), and both did give me the same error. Both scenarios kept the Sheet name in the string nm.

Here is the testing code that I was executing/stepping through to sort that out:

Dim rng As Range, i As Long
For i = 1 To ActiveWorkbook.Names.Count
    Set nm = ActiveWorkbook.Names(i)
    'Set rng = ActiveWorkbook.Names(nm.Name)
    Set rng = nm.RefersToRange
    'Debug.Print rng.Address
    MsgBox rng.Address
Next i

Using Set rng = nm.RefersToRange worked in all scenarios, while alpha-character only names worked with the Set rng = ActiveWorkbook.Names(nm.Name).

Suggestion, stick with .RefersToRange as the robust code.

Thanks for your time. Unfortunately, your code doesn't work. Is it possible that there is something wrong with my defined ranges? — HJA24, Jun 27 '19 at 07:00

Loop over all the values of the named ranges

1 Answers1