On Error Resume next not working VBA

Question

I am using the Ctrl+F simulation using Macros for find a particular number from a sheet, I have added the On error resume next code in case it fails to find the value but the error handling is not working , I am getting the following message.

Here is the code:

Sheets("Not filled").Activate

    On Error Resume Next

    Cells.Find(what:=refnumber, After:=ActiveCell, LookIn:=xlFormulas, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

possible duplicate of [Break in Class Module vs. Break on Unhandled Errors (VB6 Error Trapping, Options Setting in IDE)](http://stackoverflow.com/questions/12687105/break-in-class-module-vs-break-on-unhandled-errors-vb6-error-trapping-options) — GSerg, Aug 14 '15 at 09:35

score 1 · Answer 1 · answered Aug 14 '15 at 09:36

1

There are two possibilities:

Breaking on errors could have been turned back on, by using On Error GoTo 0 statement.
Breaking on All Errors option can be selected in Error Trapping section of VBA editor option (to check it go to Tools > Options > General > Error Trapping on VBA editor menu bar). If this option is selected, VBA compiler breaks on all errors no matter what error handling logic is applied.

answered Aug 14 '15 at 09:36

mielk

3,890
12
19

Hi, Do you have any idea why error handler would work yesterday and not today all of a sudden after a PC reboot. – Anarach Aug 14 '15 at 11:08

score 1 · Accepted Answer · answered Aug 14 '15 at 09:40

1

You are still trying to .Activate the (NOT) found cell.

Dim fnd As Range, refnumber As Long

refnumber = 123

With Sheets("Not filled")
    .Activate
    On Error Resume Next
    Set fnd = .Cells.Find(what:=refnumber, After:=ActiveCell, LookIn:=xlFormulas, _
        lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    On Error GoTo 0
    If Not fnd Is Nothing Then
        fnd.Select
    Else
        MsgBox "Not found :("
    End If
End With

This attempts to Set a Range object to the found location. If nothing was located, the fnd var is nothing.

answered Aug 14 '15 at 09:40

2

Whilst this explains @Anarach 's error, surely it doesn't explain why `on error resume next` hasn't suppressed it? – Mark Butler Aug 14 '15 at 09:42
@MarkButler Exactly, my code was working yesterday.. perfectly today its showing this weird error – Anarach Aug 14 '15 at 09:45
1

@Anarach OK, two questions: 1) What is the setting for error handling in `Tools > Options > General > Error Trapping` ? – Mark Butler Aug 14 '15 at 09:47
Wow just noticed, None of my error handlers are working – Anarach Aug 14 '15 at 09:47
@Anarach and 2) have you tried replacing `on error resume next` with `on error goto errorhandler`, and adding an `errorhandler` (eg. with a message box to test) at the bottom of your module? – Mark Butler Aug 14 '15 at 09:48
@MarkButler "Break on unhandled Errors" – Anarach Aug 14 '15 at 09:48
@MarkButler Yes , i did that just now, even that is not working , i swear to god yesterday it was perfect !! – Anarach Aug 14 '15 at 09:49
1

@Anarach - If you've been in a long period of coding and development, when was the last time you rebooted the machine? Prolonged periods of crashing code can lead to an unstable environment. – Aug 14 '15 at 09:51
@Jeeped Just now, i reboted, i had not rebooted it for days, just now i rebooted and this is not working. – Anarach Aug 14 '15 at 09:52
Ok , one thing i noticed, Jeeped's Code , i mean his method of error handling is working, none of the other error handlers are working. – Anarach Aug 14 '15 at 09:58
according to "https://msdn.microsoft.com/en-us/library/aa264506(v=vs.60).aspx" for that error to not appear i must surround it in a "with" clause. but my point is that on error was working till now .. why suddenly the error – Anarach Aug 14 '15 at 10:01
That's because in Jeeped's code there's no exception in the first place, @Anarach (which makes his answer somewhat pointless, given that you want the exception to happen). He could remove both `On Error`s and it would still work. – GSerg Aug 14 '15 at 10:01
No @Anarach, for this error to appear you don't have to surround anything with `With`. `Find()` returns `Nothing`, you call `Activate` on that, hence the error. Try ["Break in class module"](http://stackoverflow.com/a/12700250/11683). – GSerg Aug 14 '15 at 10:06
@GSerg , NO i tested his code, i gave a wrong number and his code handled by throwing a msg box , if i gave correct reference number it just ignored it. What are you meaning to convey here? – Anarach Aug 14 '15 at 10:06
@Anarach You don't seem to understand the difference between "error" from the user point of view and an exception the code works with. Your question is about raising a code-level error and handling it. This answer contains code that never raises an error and therefore never handles it. This code checks if a variable is `Nothing` or not, thus avoiding an error. While this is the correct way of doing things, it is hardly related to your question, where you want an error to happen and suppress it by handling it with `On Error Resume Next`. Please check your error handling settings again. – GSerg Aug 14 '15 at 10:11
@GSerg My settings are on "Break on unhandled errors" – Anarach Aug 14 '15 at 10:13
@Anarach Which is why [I suggested](http://stackoverflow.com/questions/32006\671/on-error-resume-next-not-working-vba/32006995?noredirect=1#comment51917442_32006995) you try [Break in class module](http://stackoverflow.com/a/12700250/11683). – GSerg Aug 14 '15 at 10:14
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/86978/discussion-between-anarach-and-gserg). – Anarach Aug 14 '15 at 10:16
Can you come over to chat room.. Please – Anarach Aug 14 '15 at 10:19
WOAH!!! , I opened a fresh workbook copy pasted some of the " On error statements" ITS FREAKING WORKING. I hate EXCEL VBA – Anarach Aug 14 '15 at 10:23

On Error Resume next not working VBA

2 Answers2