Error handling in Excel VBA
Q: I've managed to put a macro together for quite a lengthy procedure that I urgently/desperately needed on a daily basis. However, I've run into a slight problem.
In Column "G", I have dates from "G2" to "G200" that vary from day to day, in "dd/mmm" format.
Currently an Inputbox will ask the user for a date, from where the macro will search down column "G" for this date and select it. If the date is present in the column there is no problem in doing just that, however when the date entered into the InputBox is not present in column "G" I get the error message:
Microsoft Visual Basic
Run-time error "91":
Object variable or With block variable not set.
Debug?
All that is actually needed is some code that will bring up the InputBox again when the entered date is not present in column "G", enabling the user to enter another date.
Any assistance will be dearly appreciated.
A: All you need is a simple error handler, also known as an 'error trap'.
Right at the beginning of your code, just after the Sub line, enter this:
On Error Goto ErrorHandler
RestartPoint:
At the end of the code, just before the End Sub, enter this:
Exit Sub
ErrorHandler:
myAnswer = MsgBox ("Date not found - try again?", vbYesNo)
if myAnswer=vbYes then Resume RestartPoint