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
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