Sorry Dave. My mistake because I left a line out. However, to pay for my
mistake, I have tested the following and it not only finds the dates but
finds all occurrences if there is more than one match to the date. I actually
formatted them in the test macro and rng1 had no times associated with it
rng2 did have actual times.

Here is a small sample of the data I used:-
rng1 rng2
01/21/2006 01/13/2006 07:19:21
01/22/2006 01/14/2006 13:56:21
01/23/2006 01/15/2006 11:11:54
01/24/2006 01/16/2006 20:53:23
01/25/2006 01/17/2006 20:31:38
02/01/2006 01/18/2006 06:13:58
02/02/2006 01/19/2006 03:44:30
02/03/2006 01/20/2006 07:51:27
02/04/2006 01/21/2006 21:58:02
02/05/2006 01/22/2006 12:29:14
02/10/2006 01/23/2006 16:22:05

Dim DateToFind
Dim NoOfFinds
Dim InitAddress
Dim rng1 As Range
Dim rng2 As Range

Sub Find_Dates()
Sheets("Sheet1").Select
Set rng1 = Range("A2:A74") 'List of dates to find
rng1.Select
Selection.NumberFormat = "mm/dd/yyyy"
Set rng2 = Range("B2:B544")
rng2.Select
Selection.NumberFormat = "mm/dd/yyyy hh:mm:ss" 'List of dates to search
Range("A1").Select
NoOfFinds = 0
For Each cell1 In rng1
DateToFind = Format(cell1, "mm/dd/yyyy")
Application.FindFormat.NumberFormat = "mm/dd/yyyy"
Set Y = rng2.Find(What:=DateToFind, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not Y Is Nothing Then 'Y is Not Nothing indicates found target
InitAddress = Y.Address 'Save the first address
Do 'Look for further occurrences of the find criteria
NoOfFinds = NoOfFinds + 1 'For testing with Msgbox only
Y.Font.ColorIndex = 5 'For testing purposes only
Set Y = rng2.FindNext(Y)
Loop While Not Y Is Nothing And Y.Address < InitAddress
End If
Next cell1
MsgBox "Total Number of finds = " & NoOfFinds 'For testing purposes only
End Sub

Hope this answers your question.

Regards,

OssieMac


"Dave Unger" wrote:

Hi OssieMac

Thanks for your reply. The problem here is the time difference. The
data I'm looking in was entered with date & time and is formatted as
"01/28/2007 04:59:59 PM". The find list contains date only,
"01/28/07", and changing the format results in "01/28/2007 12:00:00
AM". I want the time part disregarded, and have these 2 items 'match'
on the date part.

regards,

Dave.


On Feb 8, 7:59 pm, OssieMac
wrote:
Hi Dave. You need to format the find criteria the same as the column of
dates you are looking in so try this.

Dim DateToFind

For Each cell1 In rng1
DateToFind = Format(cell1,"mm/dd/yyyy")
Set Y = rng2.Find(what:=DateToFind, LookIn:=xlValues,
lookat:=xlPart)
Next cell1



"Dave Unger" wrote:
Hello,

I have 2 columns of dates (rng1 and rng2). For each entry in rng1 I
need to find a match, if any, in rng2. Rng1 is formatted as
"01/28/07", while rng2 is "01/28/2007 05:00:00 PM".

The only way I can get the Find method to work is by formatting rng2
as serial, doing the
find, then restoring the original format. The example here seems to
be the only variation of Find that works for me. There must be a way
to avoid having to change the format.

For Each cell1 In rng1

Set Y = rng2.Find(what:=CLng(cell1), LookIn:=xlValues,
lookat:=xlPart)

Next cell1

Regards,

DaveU- Hide quoted text -

- Show quoted text -