[ 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:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,665
Office Version
365
Platform
Windows
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
23,644
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
53,746
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
23,644
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
35,177
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
23,644
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:

Watch MrExcel Video

Forum statistics

Threads
1,102,855
Messages
5,489,299
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top