[RESOLVED] How to Continue Error Trapping?

[Esham]

How do I get my error trapping to continue after it has caused an error. What happens is the error occurs, is goes to the correct trap, continues running but when there is another error, it does not trap it, it just gives the msgbox with the error. Here is the code.

Sub CheckMFGPartInSAP()
 
Dim MFGNo, ZE, Sta, Item
Dim SAPBook, LimaBook, CheckRow
LimaBook = "Copy of D35 ENGINE ASSEMBLY SPARE PARTS MASTER MATRIX.xls"
SAPBook = "SAP Dump.xls"
 
Do Until ActiveCell = ""
    MFGNo = ActiveCell
    Item = ActiveCell.Row
    Workbooks(SAPBook).Activate
    On Error GoTo NotFound
    Range("K4:K3534").Find(MFGNo, , , , , xlNext).Activate
    CheckRow = ActiveCell.Row
    If Range("A" & ActiveCell.Row) = "" Then
        Range("A" & ActiveCell.Row) = Item
    Else
        Range("A" & ActiveCell.Row) = Range("A" & ActiveCell.Row) & "," & Item
    End If
    Range("K4:K3534").Find(MFGNo, ActiveCell, , , , xlNext).Activate
    Do Until ActiveCell.Row = CheckRow
        If Range("A" & ActiveCell.Row) = "" Then
            Range("A" & ActiveCell.Row) = Item
        Else
            Range("A" & ActiveCell.Row) = Range("A" & ActiveCell.Row) & "," & Item
        End If
        Range("K4:K3534").Find(MFGNo, ActiveCell, , , , xlNext).Activate
    Loop
 
NotFound:
    'MsgBox Err.Description & Chr(10) & Err.Number
    Err.Clear
    Workbooks(LimaBook).Activate
    ActiveCell.Offset(1, 0).Activate
Loop
 
End Sub

[Pradeep1210]

I think you must Resume or Resume Next to continue error trapping.

Try This:

.
.
.
NotFound:
    'MsgBox Err.Description & Chr(10) & Err.Number
[B]    If Err > 0 Then Resume NotFound[/B]
'    Err.Clear
    Workbooks(LimaBook).Activate
    ActiveCell.Offset(1, 0).Activate
Loop

Pradeep

[Hack]

Resume Next will begin executing the code on the line immediately following the line that generated the error.

[szlamany]

Your error trap should not be in your loop:

Sub CheckMFGPartInSAP()
 
Dim MFGNo, ZE, Sta, Item
Dim SAPBook, LimaBook, CheckRow
LimaBook = "Copy of D35 ENGINE ASSEMBLY SPARE PARTS MASTER MATRIX.xls"
SAPBook = "SAP Dump.xls"
 
On Error GoTo NotFound
 
Do Until ActiveCell = ""
    MFGNo = ActiveCell
    Item = ActiveCell.Row
    Workbooks(SAPBook).Activate
    Range("K4:K3534").Find(MFGNo, , , , , xlNext).Activate
    CheckRow = ActiveCell.Row
    If Range("A" & ActiveCell.Row) = "" Then
        Range("A" & ActiveCell.Row) = Item
    Else
        Range("A" & ActiveCell.Row) = Range("A" & ActiveCell.Row) & "," & Item
    End If
    Range("K4:K3534").Find(MFGNo, ActiveCell, , , , xlNext).Activate
    Do Until ActiveCell.Row = CheckRow
        If Range("A" & ActiveCell.Row) = "" Then
            Range("A" & ActiveCell.Row) = Item
        Else
            Range("A" & ActiveCell.Row) = Range("A" & ActiveCell.Row) & "," & Item
        End If
        Range("K4:K3534").Find(MFGNo, ActiveCell, , , , xlNext).Activate
    Loop
 
BackIn:
    Workbooks(LimaBook).Activate
    ActiveCell.Offset(1, 0).Activate
Loop
 
NotFound:
    'MsgBox Err.Description & Chr(10) & Err.Number
    Err.Clear
    Resume BackIn
 
End Sub

I wasn't sure if the Workbooks(Lima...) or ActiveCell... parts should be in the error trap or not...

[DKenny]

I assume you are Error trapping for when the searched for value is not found?

You don't need to trap for that at all as the Find method returns Nothing if the value is not found.
Have a look at my posts here here to see how to trap for Nothing

While I'm here, all your varaiables are declared as variants - not a good idea. If you had setup your workbooks as object variables, you could make this code much simpler.

[Esham]

Resume to a specific location worked. Thanks

[Esham]

I assume you are Error trapping for when the searched for value is not found?

You don't need to trap for that at all as the Find method returns Nothing if the value is not found.
Have a look at my posts here here to see how to trap for Nothing

While I'm here, all your varaiables are declared as variants - not a good idea. If you had setup your workbooks as object variables, you could make this code much simpler.

Sorry DKenny but the Find method will not return nothing. If it does not find anything then it returns an error. Your post reference does not even use the Find method. I was in a hurry to write the macro and that is why I didn't define all my variables. I don't like having set all my objects to nothing if I forget about one.

[DKenny]

Per the VBA help file.
"Finds specific information in a range, and returns a Range object that represents the first cell where that information is found. Returns Nothing if no match is found. Doesn’t affect the selection or the active cell."

[Esham]

Per the VBA help file.
"Finds specific information in a range, and returns a Range object that represents the first cell where that information is found. Returns Nothing if no match is found. Doesn’t affect the selection or the active cell."

Ok, sorry DKenny, I didn't see the previous code example from the reference post you submitted. I now see how it is set and checked. Thanks for the help!

[DKenny]

This might also help.

Sub NewCheckMFGPartInSAP()
Dim wkbLima As Workbook
Dim wkbSAP As Workbook
Dim rngChkCell As Range
Dim rnMatchArea As Range
Dim rngMatch As Range
Dim lMFGNo As Long
Dim rngItem As Range
        
    Set wkbLima = Workbooks("Copy of D35 ENGINE ASSEMBLY SPARE PARTS MASTER MATRIX")
    Set wkbSAP = Workbooks("SAP Dump")
    
    Set rngChkCell = wkbLima.Worksheets(1).Range("A1")
    Set rngMatcharea = wkbSAP.Worksheets(1).Range("K4:K3534")
    
    Do While rngChkCell.Value <> ""
        
        lMFGNo = rngChkCell.Value
        Set rngMatch = rnMatchArea.Find(What:=lMFGNo, LookIn:=xlValues)
        
        If Not (rngMatch Is Nothing) Then
            'rest of code here
        End If
    Loop
    
    Set rngMatch = Nothing
    Set rngMatcharea = Nothing
    Set rngChkCell = Nothing
    Set wkbSAP = Nothing
    Set wkbLima = Nothing
End Sub