Find a date in a list

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

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


"Leonard615" wrote:

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

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

"Martin P" wrote:

> 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.
>
>
> "Leonard615" wrote:
>
> > 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.

[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

Leonard615 wrote:
> 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?
>
> "Martin P" wrote:
>
>
>>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.
>>
>>
>>"Leonard615" wrote:
>>
>>
>>>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.

[Leonard615]

This worked perfectly. Thanks so much for the help.

"Rowan Drummond" wrote:

> 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
>
> Leonard615 wrote:
> > 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?
> >
> > "Martin P" wrote:
> >
> >
> >>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.
> >>
> >>
> >>"Leonard615" wrote:
> >>
> >>
> >>>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.
>

[Rowan Drummond]

You're welcome.

Leonard615 wrote:
> This worked perfectly. Thanks so much for the help.
>