Error handling

[gavinkelly]

I have having trouble with error handling:

On Error GoTo ErrorHandler
For y = 2 To 25

    For x = 2 To 9
        i = WorksheetFunction.Find(Cells(x, 4), Cells(y, 1), 1)
        Cells(y, 2) = Cells(x, 5)
        Exit For
ErrorHandler:
     Next x
Next y

The first error is ignored as it goes to ErrorHandler and so resumes the loop but the next time there is an error in the loop the code stops i.e. it does not go to ErrorHandler. I guess this is to do with errors in loops.

any suggestions to make the error always got to the handler?

 

[mikerickson]

Rather than using the spreadsheet FIND, you could use the VB InStr and not need error handling.

For y = 2 To 25
    For x = 2 To 9
        If 0 < InStr(Cells(y,1),Cells(x, 4)) Then
            Cells(y, 2) = Cells(x, 5)
            Exit For
        End If
     Next x
Next y

 

[RoryA]

If you want to go back to normal code processing from an error handler, you have to use the Resume statement.

 

[gavinkelly]

I am unsure how to use the Resume statement to make it work, i have tried excel VBA help but i am still struggling. Where does it go in my code?

 

[RoryA]

It doesn't really - I was just making a general point. You would be better off using the version provided by mikerickson above, but since you effectively want to just ignore the error you could use On Error Resume Next:

On Error Resume Next
For y = 2 To 25

    For x = 2 To 9
        i = WorksheetFunction.Find(Cells(x, 4), Cells(y, 1), 1)
        If Err.Number = 0 then
            Cells(y, 2) = Cells(x, 5)
            Exit For
        Else
            Err.Clear
        End If
     Next x

Next y

though it would be quicker to use the range.Find method rather than looping.
</pre>

 

[Makrini]

2 ways..

Either in your normal code before running what could cause an error...

On Error Resume Next


Or...

On Error Goto ErrorHandler

code..

ErrorHandler:
Resume Next


but the previous poster was correct - use Instr function so you don't have to rely on Error catching in this case

 

[RoryA]

FYI, the reason you have to use Resume if you use an error handler section, is that if you don't, VBA considers the error handler code to still be running. If an error is raised within an error handling routine, you just get a run time error (otherwise you would end up in an infinite loop).