[ on error go to ] function

kraaniks

New Member
Joined
Sep 19, 2016
Messages
26
Dear excel users,

Is this syntax correct ? I am getting debugging error second time error comes, however it ignores the error for the first time. I want it to skip all errors not just to stop at second.

I have not used exit sub or on error go to 0 statements.


Code:
For var_country = 1 To 4
For var_functions = 1 To 2

Functions(1) = "AP"
Functions(2) = "AR"


country(1) = "NO-"
country(2) = "SE-"
country(3) = "DK-"
country(4) = "PL-"

result = country(var_country) & Functions(var_functions)

On Error GoTo Errorhandler:


'MY CODE HERE

Errorhandler: 

Next var_functions


Next var_country




End Sub
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,278
Why haven't you used On Error Goto 0?

That 'resets' the error handler, which is what you probably want to do.

By the way, what error(s) are you trying to handle?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,693
You need to clear an error after you've trapped it.

Code:
On Error Goto To Handler

For var_functions = 1 To 4
    For var_country = 1 to 2
        ' code

Handler:
    Err.Clear
    Next var_country
Next var_functions

On Error Goto 0
 

kraaniks

New Member
Joined
Sep 19, 2016
Messages
26
I have a cells.find(what:=result) function which searches for "result" based on above code in a sheet, if it doesnt find the value for the second time it gives me debugging, it ignores first time it appears.

So I need to use on error go to 0 somewhere?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,148
Office Version
365
Platform
Windows
You usually also want to put an "Exit Sub" row just before the Errorhandler code.
That way, if your procedure runs through all the way without finding any errors, it exits the code before hitting the error handling code at the end.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,693
Rather than using On Error, you can test if if .Finds nothing


Code:
for var_country = 1 to 2
    ' code    

    Set myCell = .Find(want:= result)

    If myCell Is Nothing Then

       ' process my cell
    Else
Next var_country
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,178
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You don't need error handling for that. Assign the result of the Find operation to a Range variable then test if it Is Nothing before you try and access any of its properties.

As regards your specific question about error handling, neither Err.Clear nor On Error Goto 0 will actually clear the current exception state - you should use a Resume statement for that. See here.
 
Last edited:

kraaniks

New Member
Joined
Sep 19, 2016
Messages
26
I want to continue with "next" in both instances, if there is an error, by skipping the core code, and if there is no error then run through all the code. So then it would look like this: IF my cell is nothing then next, Else code, and then next. I dont think thats possible.

And clearing error with err.clear or on error goto 0 doesnt work.
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,693
You just put the bulk of you code inside the "found cell is not Nothing" branch of an if


Code:
For num_County = 1 to 2

    Set myCell = someRange.Find(what:="something")

    If myCell is Nothing Then
        ' do nothing
    Else
        ' code to do something
    End If
Next num_country
 
Last edited:

Forum statistics

Threads
1,078,252
Messages
5,339,097
Members
399,277
Latest member
Jyoti C

Some videos you may like

This Week's Hot Topics

Top