finding date in range of dates

[Norbert]

while recording a macro, I experience a problem:

I copy a cell with a date (e.g. 29.09.2008)
then I change to a different worksheet and paste into cell A1.
Then, I do: Edit, Find and I paste the date into the Find window.
When I click on OK, the correct cell is selected.

The problem is following: The macro records the value: 29.09.2008
The date I am searching for is variable and not always the same.

How can I solve this problem?

Thanks,
Norbert

 

[Mike H.]

Sub FindaDate()

Dim DateToFind As Date

'after you've copied and gone to the sheet to paste your date:

Range("A1").Select
ActiveSheet.Paste
Let DateToFind = ActiveCell.Value
Cells.Find(What:=DateToFind, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub

 

[Norbert]

Thank you very much.
Works like a bomb.

Regards,
Norbert