VBA Find Question

[Emily]

The last two statement works in Office XP but not in Offce 2000, why?

Set FourWeeksCycle = Range("D9:AE9")
Set PHDate = Sheets("PH").Range("A2:A18")
For Each PHFind In FourWeeksCycle
If Application.CountIf(PHDate, PHFind.Value) = 1 Then 'Replace the code below
'Set f = PHDate.Find(PHFind, lookat:=xlPart
'If Not f Is Nothing Then
This message was edited by Emily on 2002-10-22 11:34

 

[Andrew Poulsom]

How are your dates formatted?

1. If dd/mm/yyyy it works with:

Set f = PHDate.Find(PHFind.Value, lookat:=xlPart)

2. If formatted as d-mmm-yy it works with:

Set f = PHDate.Find(PHFind.Text, lookat:=xlPart)

although I did have a problem in that 1-Oct-02 found 11-Oct-02. 2-Oct-02 was correct.

3. I could not get it to work with dd-mmm-yy.

Weird! If you ask me there are some serious
bugs with UK/European dates in Excel 2000.