I am working on a project that requires the user to input a start date into an input box and then searches a worksheet to find the date. Once it finds the start date, it establishes a variable called StartRow as ActiveCell.Row. I will then use StartRow for further processing.
I was having trouble when a user would enter a date of 9/01/2009 instead of 9/1/2009. I then found code online to assist me with the search. I am new to VB and do not understand the entire code I am using but understand some of it well enough to modify it for my uses. I have it working except when I enter a date not found, I cannot get an error message to show telling the user they entered a date not found. I would like for it to display an error message and then give the user the option of entering the date again or exiting.
I have figured a workaround but really do not like it and would prefer to handle this correctly.
If anyone can help me handing errors with the code pasted below from the attached sample spreadsheet, I would appreciate it
[VBA]
Sub FindDate()
Dim strdate As String
Dim rCell As Range
Dim lReply As Long
Dim StartRow As Long
StartRow = 0
Sheets("Calendar").Select
Range("A1").Select
strdate = Application.InputBox(Prompt:="Enter a Date to Locate on This Worksheet", _
Title:="DATE FIND", Default:=Format(Date, "Short Date"), Type:=1)
'Cancelled
If strdate = "False" Then Exit Sub
strdate = Format(strdate, "Short Date")
On Error Resume Next
Cells.Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
StartRow = ActiveCell.Row
If StartRow = 1 Then
MsgBox (" Date was not Found - Please Check Date and try again")
Exit Sub
Else
MsgBox ("StartRow is: " & StartRow)
End If
End Sub
[/VBA]