Getting Error prompt even though I am using an ON ERROR GOTO line

[Chris Macro]

Not sure why my code is breaking on "Set Test" line when I have an On Error statement. The NamedRange = "=REF!#REF!#" when this occurs. Any ideas?

(Part of code):

For Each NamedRange In ActiveWorkbook.Names        'Get rid of ranges that don't exist anymore
            On Error GoTo NoRangeExists
             Set Test = Range(NamedRange)
            On Error GoTo 0

 

[Firefly2012]

Why only provide part of the code? The important bit is likely to be what you have under NoRangeExists label

 

[Chris Macro]

It doesn't even go to NoRangeExists. It stops on the line "Set Test"

 

[Firefly2012]

But does it go to NoRsngeExists the very first time an invalid range is encountered? Could you actually post the entire code?

 

[Chris Macro]

Here's the entire loop:

<font face=Calibri><SPAN style="color:#007F00">'Find all Named Ranges</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> NamedRange <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Names<br>        <SPAN style="color:#007F00">'Get rid of ranges that don't exist anymore</SPAN><br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> NoRangeExists<br>             <SPAN style="color:#00007F">Set</SPAN> Test = Range(NamedRange)<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        <br>        <SPAN style="color:#00007F">If</SPAN> NamedRange <> "=#NAME?" <SPAN style="color:#00007F">Then</SPAN><br>            NmRng = NamedRange.Name<br>            <SPAN style="color:#00007F">Set</SPAN> NewRow = Table.ListRows.Add(AlwaysInsert:=True)<br>                <SPAN style="color:#00007F">If</SPAN> Left(NmRng, 1) = "'" Then NmRng = "'" & NmRng <SPAN style="color:#007F00">'Keeps apostrophe showing</SPAN><br>            NewRow.Range(1, 1).Value = NmRng<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>NoRangeExists:<br><br>    <SPAN style="color:#00007F">Next</SPAN> NamedRange</FONT>

 

[Firefly2012]

Thank you for posting the code - the issue you are seeing is down to not having a Resume statement once you are in error handling mode (once the first error is encountered). You can correct this by changing the code slightly to use an On Error Resume Next and checking whether Test Is Nothing:

'Find all Named Ranges
    For Each NamedRange In ActiveWorkbook.Names
        'Get rid of ranges that don't exist anymore
            On Error Resume Next
             Set Test = Range(NamedRange)
            On Error GoTo 0
        
        'check if Test is nothing or not - if it is nothing skip next section:
        If Not Test Is Nothing Then
           If NamedRange <> "=#NAME?" Then
               NmRng = NamedRange.Name
               Set NewRow = Table.ListRows.Add(AlwaysInsert:=True)
                If Left(NmRng, 1) = "'" Then NmRng = "'" & NmRng 'Keeps apostrophe showing
               NewRow.Range(1, 1).Value = NmRng
           End If
        End If
        
    Next NamedRange

 

[Chris Macro]

Thanks FireFly!!! I believe that solved all my issues. I just had to make one minor adjustment to your suggested code and it now works perfectly. Thank you so much for your help!!!

'Get rid of ranges that don't exist anymore
            On Error Resume Next
                Set Test = Nothing
                Set Test = Range(NamedRange)
            On Error GoTo 0

 

[Firefly2012]

You're welcome