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
Office Version
  1. 365
Platform
  1. Windows
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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
 

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,343
Office Version
  1. 365
Platform
  1. Windows
It doesn't even go to NoRangeExists. It stops on the line "Set Test"
 

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
Office Version
  1. 365
Platform
  1. Windows
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
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,172,125
Messages
5,879,205
Members
433,409
Latest member
momo2809

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