Hi OssieMac,

Thanks again. However, you must be using a newer version of Excel,
I'm using 97 and the FindFormat function isn't available. And I am
aware that the Find arguments should be set each time, just wanted to
keep the sample code as 'bare bones' as possible to illustrate my
approach. Appreciate your help.

regards,

Dave





On Feb 10, 5:15 am, OssieMac
wrote:
After thought. Include the following line at end of module.

Application.FindFormat.Clear

Also your code only included a minimum of arguments in the Find method. To
avoid problems, set all these arguments explicitly each time you use this
method because they are saved from previous finds and if you don't reset them
then the code may not work as you expect. Look up "Find Method" in the help
for VBA Editor and click on show all and read Remarks.



"OssieMac" wrote:
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 -- Hide quoted text -

- Show quoted text -