PDA

View Full Version : Instructions for Explicit Error Type



YellowLabPro
11-10-2007, 05:27 AM
Rather than use the On Error Resume Next method, I would like to instruct based on an explicit error code, error code 91.
My code is not written properly to do so.
Currently when this error is encountered, it does not execute the error instruction.
Could I get someone to show me how to write this properly?


Set wsSource = Workbooks("Missing Images List.xls").Worksheets("MissingImages")
Set wsTarget = Workbooks("Complete_Upload_File_Green.xls").Worksheets("EC Products")
Set colA = wsTarget.Columns("A")
Lrow = wsSource.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = wsSource.Range("A4:A" & Lrow)
For Each c In rng
If Error = 91 Then
c.Offset(0, 4).Value = "Deprecated Product"
End If
On Error Resume Next
Set cel = colA.Find(c.Value)
cel.Offset(0, 3).Value = "found"
'MsgBox cel.Address(0, 0)
'MsgBox c.Value
Err.Clear
Next c
End Sub


Thanks

Bob Phillips
11-10-2007, 05:40 AM
I think you want something like this



Set wsSource = Workbooks("Missing Images List.xls").Worksheets("MissingImages")
Set wsTarget = Workbooks("Complete_Upload_File_Green.xls").Worksheets("EC Products")
Set colA = wsTarget.Columns("A")
Lrow = wsSource.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = wsSource.Range("A4:A" & Lrow)
For Each c In rng
On Error Resume Next
Set cel = colA.Find(c.Value)
cel.Offset(0, 3).Value = "found"
If Err.Number = 91 Then
c.Offset(0, 4).Value = "Deprecated Product"
End If
On Error GoTo 0
Next c

Bob Phillips
11-10-2007, 05:44 AM
This is a more generalised method for error handling, which can be expanded to a full application approach



Sub TestProcedure()
Const mpProcedure As String = "TestProcedure"

On Error GoTo proc_error

Debug.Print 1 / 0

proc_exit:
Exit Sub

proc_error:
If Err.Number = 11 Then
Range("A1").Offset(0, 4).Value = "Deprecated Product"
Resume Next
Else
MsgBox "Error: " & Err.Number & " in " & mpProcedure
End If

End Sub

YellowLabPro
11-10-2007, 06:13 AM
Good Morning Bob,
Thanks. Option 1- Works as I need it.
After reading through yours and compared it to mine, mine has not encountered the error yet. Yours' waits for the error, identifies explicitly the one I know is inevitable and then handles it.

Option 2- I have questions here, but I will await to see if there is anything you want to add to my reply.

Bob Phillips
11-10-2007, 06:16 AM
Morning Doug,

That is right. The problem that I see with yours is that as soon as the error happens, you clear it, so you will never pick it up in the next iteration of the loop (and it doesn't even attempt to if it is the last cell in the range).

rory
11-12-2007, 10:49 AM
Why not avoid the error:

For Each c In rng
Set cel = colA.Find(c.Value)
If cel Is Nothing Then
c.Offset(0, 4).Value = "Deprecated Product"
Else
cel.Offset(0, 3).Value = "found"
End If
Set cel = Nothing
Next c

Norie
11-12-2007, 01:28 PM
Doug

I agree with rory, avoid the error.:)

If you use error handling to try and avoid specific errors you might actually be hiding other errors.

johnske
11-12-2007, 07:41 PM
Agree totally, 'avoiding' errors is generally the best error-handling practice - if you cater for all likely error scenarios properly you won't need any so-called second-rate 'error-handling' procedure which just avoid the real issue altogether.