Microsoft Excel - On Error Resume Next is not catcing the error

Asked By Rajender Prasad on 21-May-13 10:53 AM

Dear All,

In the below code "On Error Resume Next" is not working. My first value is BDP if am not able to find that next value is SECURE, it should go to next. but is not happeeing. Please help

Sub FindLob()

    Dim cl As Range
    Dim ws As Worksheet
    Dim SearchString As String
    Dim search(1 To 5) As String
'    Workbooks("cosmos access thru vba.xlsm").Sheets(5).Activate
    'Sheets("calcSheet").Activate

    Set ws = Worksheets("Cal")
    ActiveSheet.Range("A1").Select
    Application.FindFormat.Clear

' Find first instance on sheet
On Error GoTo Process:

    Set cl = ws.Cells.Find(What:=".com", After:=ws.Cells(1, 1), _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    cl.Activate

    'FindText (".com")
    ActiveCell.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=TRIM(RC[-1])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.FormulaR1C1 = "=MID(RC[-1],FIND("" "",RC[-1],1)+1,40)"
      GpsData(6) = ActiveCell.Value    'Assign HICN to variable
    'Selection.ClearContents

If GpsData(6) = "" Then

Process:
On Error GoTo 0
search(1) = "BDP"
search(2) = "SECURE"

    Range("B1").Activate
    ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])"
    Range("B1").Select
    Selection.Copy
    Range("B1:B300").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
    Application.CutCopyMode = False


    For i = 1 To 5
      'MsgBox ActiveCell.Rows.Address
      On Error Resume Next

        Selection.Find(What:=search(1), After:=ActiveCell, LookIn:= _
      xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
      xlNext, MatchCase:=False, SearchFormat:=False).Activate



        If ActiveCell.Rows.Address <> "$B$1" Then
        Location = ActiveCell.Rows.Address
        GpsData(6) = ActiveCell.Value
        Exit For
      End If

    Next


End If


End Sub

Harry Boughen replied to Rajender Prasad on 21-May-13 06:52 PM

Hello Prasad,
Have you solved your previous question? The previous code you posted looks to be part of this.
I am not sure what you are trying to do in the loop in question but I wonder whether it is because the code opts out of the loop at the Exit For statement.
Regards
Harry

Bill B replied to Harry Boughen on 17-Jun-13 11:55 PM

fyi, most don't understand how it works because nobody every spells it out
here it is:

here's how to use error handling in this case

function something()
on error goto YourLabel:

'do something with an error

CleanExit:
'log your error and maybe do something, but by having a clean exit, it won't bubble up outside this
'if you don't do it this way, the second error will pop to the caller level... which you might not have trapped
'and the third error will pop the entire app! well, it could be the 2nd error...

exit function
YourLabel:

    e(0) = Err.Description: lerr = Err.Number
'why dual line? catch the string and the number
resume CleanExit

end function

or maybe something like this

private sub whatever()
On Error GoTo Command_Clickerr
Dim e(1) As String, lerr As Long


'do something

Command_ClickClean:
On Error Resume Next
If lerr <> 0 Then
    e(1) = "Form Form1.Command_Click"
'    MsgBox e(0), vbExclamation, e(1)
'    LogSomeError e, lerr
'    RaiseEvent SysError(lerr, e(0), e(1))
   ComPlusErrorEvent e(1), e(0), lerr + vbObjectError
   Erase e
End If
Exit Sub
Command_Clickerr:
    e(0) = Err.Description: lerr = Err.Number
Resume Command_ClickClean
end sub

Bill B replied to Rajender Prasad on 17-Jun-13 11:56 PM

fyi, most don't understand how it works because nobody every spells it out
here it is:

here's how to use error handling in this case

function something()
on error goto YourLabel:

'do something with an error

CleanExit:
'log your error and maybe do something, but by having a clean exit, it won't bubble up outside this
'if you don't do it this way, the second error will pop to the caller level... which you might not have trapped
'and the third error will pop the entire app! well, it could be the 2nd error...

exit function
YourLabel:

    e(0) = Err.Description: lerr = Err.Number
'why dual line? catch the string and the number
resume CleanExit

end function

or maybe something like this

private sub whatever()
On Error GoTo Command_Clickerr
Dim e(1) As String, lerr As Long


'do something

Command_ClickClean:
On Error Resume Next
If lerr <> 0 Then
    e(1) = "Form Form1.Command_Click"
'    MsgBox e(0), vbExclamation, e(1)
'    LogSomeError e, lerr
'    RaiseEvent SysError(lerr, e(0), e(1))
   ComPlusErrorEvent e(1), e(0), lerr + vbObjectError
   Erase e
End If
Exit Sub
Command_Clickerr:
    e(0) = Err.Description: lerr = Err.Number
Resume Command_ClickClean
end sub