vanhunk
11-08-2012, 05:48 AM
: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
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