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

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,343
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):

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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
Why only provide part of the code? The important bit is likely to be what you have under NoRangeExists label
 

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
But does it go to NoRsngeExists the very first time an invalid range is encountered? Could you actually post the entire code?
 

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,343

ADVERTISEMENT

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

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
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:

Code:
'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

Well-known Member
Joined
Nov 2, 2011
Messages
1,343
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!!!

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

Watch MrExcel Video

Forum statistics

Threads
1,133,527
Messages
5,659,335
Members
418,497
Latest member
VAllen79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top