Find a date in a list
 

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.

Find a date in a list
 

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.

Find a date in a list
 

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.

Find a date in a list
 

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.

Find a date in a list
 

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.

Find a date in a list
 

You're welcome.

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

Find a date in a list
 

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?

"Rowan Drummond" wrote:

You're welcome.

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

Find a date in a list
 

Pls stay in the ORIGINAL thread

--
Don Guillett
SalesAid Software

"Mike Milmoe" wrote in message
...
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?

"Rowan Drummond" wrote:

You're welcome.

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