Find a date in a list

L

Leonard615

I have a worksheet with 12 columns of information, one of which is dates
related to the information. The dates are not in order chronologically. I'd
like a macro asking the user to enter his desired date and have the macro
take him to the first occurrence of that date. Tried using Find, but haven't
been able to figure out how to make it work for dates. Any suggestions would
be greatly appreciated.
 
M

Martin P

If the dates are in column A, enter the follwing in cell A1:
=TEXT(A1,"dd")&TEXT(A1,"mmmm")&TEXT(A1,"yyyy")
Copy down.
Enter something like 19december2004 in the Find What field. Remember to
choose Values under Options.
 
L

Leonard615

Thank you. This does work, but for other reasons I would like to "Find"
using just the date. Is there another way to do this without additional
cells or formulae?
 
R

Rowan Drummond

Try:

Sub test()
Dim strDate As String
Dim dDate As Date
Dim fndDate As Range
strDate = InputBox("Enter the Date to Find")
On Error Resume Next
dDate = CDate(strDate)
On Error GoTo 0
If Not dDate = Empty Then
With Sheets(1).Columns(1) 'change sheet and column as required
Set fndDate = .Find(dDate)
End With
If Not fndDate Is Nothing Then
fndDate.Activate 'or whatever
Else
MsgBox "Date not found"
End If
Else
MsgBox "You must enter a valid date"
End If
End Sub

Hope this helps
Rowan
 
M

Mike Milmoe

This works perfectly for me as well. I have a further question. How can you
make the date to find the current date, essentially not asking for the date
with a pop-up?
 
Top