[RESOLVED] How to Continue Error Trapping?
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.
VB 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
Re: How to Continue Error Trapping?
I think you must Resume or Resume Next to continue error trapping.
Try This:
VB Code:
.
.
.
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 :)
Re: How to Continue Error Trapping?
Resume Next will begin executing the code on the line immediately following the line that generated the error.
Re: How to Continue Error Trapping?
Your error trap should not be in your loop:
VB 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"
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...
Re: How to Continue Error Trapping?
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.
Re: How to Continue Error Trapping?
Resume to a specific location worked. Thanks
Re: How to Continue Error Trapping?
Quote:
Originally Posted by 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.
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.
Re: How to Continue Error Trapping?
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."
Re: How to Continue Error Trapping?
Quote:
Originally Posted by 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."
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!
Re: [RESOLVED] How to Continue Error Trapping?
This might also help.
VB Code:
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
