Find with a variable
Sometimes VBA and dates don't play nice:
Option Explicit
Sub testme01()
Dim myDate As String
Dim FoundCell As Range
Dim RngToSearch As Range
myDate = InputBox(Prompt:="enter a date")
If myDate = "" Then
Exit Sub
End If
If Year(CDate(myDate)) < 1990 _
Or Year(CDate(myDate)) 2010 Then
MsgBox "Please enter a nice date"
Exit Sub
End If
With Worksheets("sheet1")
Set RngToSearch = .UsedRange
With RngToSearch
Set FoundCell = .Cells.Find(what:=CDate(myDate), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With
End With
If FoundCell Is Nothing Then
MsgBox Format(myDate, "mmmm dd, yyyy") & " was not found"
Else
MsgBox "Found it: " & FoundCell.Address(0, 0)
End If
End Sub
If this doesn't work, you could try changing:
..Cells.Find(what:=CDate(myDate), _
to
..Cells.Find(what:=clng(CDate(myDate)), _
Chris wrote:
Having a few problems with my syntax and would appreciate any help.
I want to write a macro that will find any date I specify in my spreadsheet,
I can enter the date variable using InputBox and I can do a specific find
with the Find command but I don't know how to pass the date variable entered
into the InputBox into the Find command.
--
Regards,
Chris
--
Dave Peterson
|