MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Message box instead of debbuger!!


Posted by Ben on April 05, 2001 2:44 PM

I have wrote the macro below. The macro searches for a product number that is in cell D4 on the ProcessDelivery sheet in the OnOrder sheet range A1:2000. But if it cannot find it, it displays a error message and runs the debugger.

How do I stop it running the debugger if it cannot find the product code and instead stop the macro and display the message "Sorry! The Product code you have entered cannot be found" is a message box?

Sub ProcessArrival()

Dim cell As Range
For Each cell In Sheets("OnOrder").Range("A1:A2000")
If cell.Offset(0, 0) = Sheets("ProcessDelivery").Range("D4") Then
cell.EntireRow.Delete
End If
Next
Application.CutCopyMode = False
End Sub

Thanks
Ben


Posted by Barrie Davidson on April 05, 2001 3:05 PM

Try this Ben.
Sub ProcessArrival()

Dim cell As Range
OnError GoTo ErrorMessage
For Each cell In Sheets("OnOrder").Range("A1:A2000")
If cell.Offset(0, 0) = Sheets("ProcessDelivery").Range("D4") Then
cell.EntireRow.Delete
End If
Next
Application.CutCopyMode = False
Exit sub
ErrorMessage:
Msgbox Prompt:="Sorry! The Product code you have entered cannot be found"
End Sub

Posted by Ben on April 05, 2001 3:17 PM

Thanks Again

Thanks Again