PDA

View Full Version : Solved: Date Search - error handling



bdsii
12-31-2009, 09:57 AM
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. :help

If anyone can help me handing errors with the code pasted below from the attached sample spreadsheet, I would appreciate it :yes



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

Bob Phillips
12-31-2009, 10:58 AM
Sub FindDate()
Dim strdate As String
Dim rCell As Range
Dim lReply As Long
Dim StartRow As Long

Sheets("Calendar").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

Set rCell = Cells.Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If rCell Is Nothing Then

MsgBox (" Date was not Found - Please Check Date and try again")
Exit Sub
Else

StartRow = rCell.Row
MsgBox ("StartRow is: " & StartRow)
End If

End Sub

bdsii
12-31-2009, 11:58 AM
Great! That worked !! Thanks xld :hi:

Marking this one solved