date not found in range

[Jupiler]

Hello, excellians. In range A1 to A45 I've got dates starting from 22-1-2009 til 25-2-2009.
When I try this procedure and I enter a value like 22-2-2009, this date is never found.
Can anybody show me the error in next procedure.

Sub tryout()
Dim myrange As Range
Dim mycell As Range
Dim dSearch As Date


dSearch = Now()
dSearch = InputBox("enter date")
Set myrange = Range("a1:a45")

On Error Resume Next
Set mycell = myrange.Find(what:=dSearch, searchformat:="ddmmyyyy", lookat:=xlWhole)
On Error GoTo 0
If Not mycell Is Nothing Then
MsgBox "ok"
Else
MsgBox "nothing found"
End If
End Sub


Thanks in advance.

 

[Domski]

Try without the searchformat bit:

Sub tryout()
Dim myrange As Range
Dim mycell As Range
Dim dSearch As Date

dSearch = Now()
dSearch = InputBox("enter date")
Set myrange = Range("a1:a45")
On Error Resume Next
Set mycell = myrange.Find(what:=dSearch, lookat:=xlWhole)
On Error GoTo 0
If Not mycell Is Nothing Then
MsgBox "ok"
Else
MsgBox "nothing found"
End If
End Sub

Dom

 

[Jupiler]

Thanks for the reply but this doesn't work either.

 

[MickG]

Hi, This may not help,but that's what I tried & it worked for me.
Mick

 

[Andrew Poulsom]

This worked for me:

Sub tryout()
    Dim myrange As Range
    Dim mycell As Range
    Dim dSearch As Date
    dSearch = Now()
    dSearch = InputBox("enter date")
    Set myrange = Range("a1:a45")
    On Error Resume Next
    Set mycell = myrange.Find(what:=DateValue(dSearch), LookIn:=xlFormulas, lookat:=xlWhole)
    On Error GoTo 0
    If Not mycell Is Nothing Then
        MsgBox "Found in " & mycell.Address
    Else
        MsgBox "nothing found"
    End If
End Sub

 

[Jupiler]

Sorry, doesn't work. Maybe it's something with the format. Maybe I should try first with ordinary strings instead of date.
Thanks anyway.

 

[Andrew Poulsom]

Find can be tricky with dates, particularly if you are in the UK like me. However, to test my code I entered the 22 Jan 2009 to 25 Feb 2009 in A1:A35 and formatted the cells as dd-mm-yyyy. Whe I ran my code and entered 22-2-2009, the message box said "Found in $A$32".

Are you sure your dates are serial dates not text? Other than that possibility, I don't know why it's not working for you, sorry.

 

[Jupiler]

I started all over again and ... now it works. Thank you Mr. Poulsom. Seems that even
in Belgium find with dates can be tricky.
Greetings!