Solved: Error Handling strange behaviour, it only works the first time

[vanhunk]

:dunno
Good day,

I have used Error handling to prevent the macro from crashing and it works fine, except if you branch back to the body of the macro and hit the same error provoking condition again.!?!?

The code below is used to search through a data base of chemicals and to extract the row of data for the chemical if found. The error handling is to prevent the macro from crashing if the chemical searched for is not present in the list.

If the chemical searched for (search string) is not found, the operator is asked for a new search string, if the new search string is present the macro works fine, HOWEVER, if the new search string is also not present, the ERROR HANDLER is IGNORED!!!

I would appreciate any help to sort out this, for me, STRANGE BEHAVIOUR.



Sub Search4Chemical()
' This Macro assists with finding a specific chemical in the CCPS chemicals list.
Dim mySearchString As String
Dim varAnswer As String
Dim varAnswer2 As String

SelectSearchString:
mySearchString = Application.InputBox(prompt:="Type Search String" & vbCrLf & vbCrLf & "e.g. " & """" & "ethylene" & """", _
Title:="Search for chemical", Default:="ethylene", Type:=2)

If mySearchString = "False" Then Exit Sub

Set SearchRange = Worksheets("CCPS Chemicals").Range("A14" & ":" & "LaasteTerm")

On Error GoTo ErrorHandler1
SearchRange.Find(What:=mySearchString, _
After:=Range("A14"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

Worksheets("CCPS Chemicals").Range("J1") = ActiveCell.Row

'On Error GoTo 0 'Disables any enabled error handler

FindNextString:

varAnswer = MsgBox("Continue Search?", vbYesNo, "Continue to next occurrence?")

If varAnswer = 7 Then Exit Sub

SearchRange.Find(What:=mySearchString, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

Worksheets("CCPS Chemicals").Range("J1") = ActiveCell.Row

If Range("J1").Value > ActiveCell.Row Then Exit Sub

If varAnswer = vbYes Then GoTo FindNextString

Exit Sub

ErrorHandler1:

varAnswer2 = MsgBox("Search String not found, do you want to continue?", _
vbYesNo, "Select New Search String")

If varAnswer2 = 7 Then Exit Sub

GoTo SelectSearchString

End Sub

[Aflatoon]

You can't use Goto to exit an error handler routine - you have to use a resume statement otherwise everything after the error point is considered an error handler and errors in error handlers are always unhandled. You could simply replace Goto with Resume in the error handler, or rewrite the code to test if the find is successful before attempting to activate or otherwise use the found cell.

[vanhunk]

Thank you Aflatoon,

It worked perfectly.

Regards