vba code differance excel 2000 excel 2003 xlup

[nehpets12]

I am using the following code on excel 2000 all works fine but when I use it on excell 2003 It does not find the value Mydate in the UP direction xlup

any ideas on code change required ?

    Mydate = Range("B1").Value
    Range("F:F").Select
    Selection.Find(What:=Mydate, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlUp, _
        MatchCase:=False).Activate
     Range(Rows(ActiveCell.Row + 1), Rows("65536")).Select

[/quote]

 

[Richard Schollar]

Hi

I assume you want to find the last occurrence of MyDate in column F:F? So if F1 and F2 had MyDate in them, then F2 would be identified? If so, try this:

Mydate = Range("B1").Value
    Range("F:F").Select
    Range("F65536").Activate
    Selection.Find(What:=Mydate, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlUp, _
        MatchCase:=False).Activate
     Range(Rows(ActiveCell.Row + 1), Rows("65536")).Select

Do note, however, that it is rarely necessary to actually Select anything within VBA, and code efficiency can be improved by avoiding its use.

Best regards

Richard

 

[nehpets12]

Even changing to new code works perfectly in excel 2000 but when I run it on excel 2003 it finds "5" in row 22 and then selects rows 23 down

It should find row 30 and select rows 31 down

Test Darren.xls
	B	C	D	E	F
1	5				1
2					1
3					1
4					1
5					2
6					2
7					2
8					2
9					2
10					2
11					2
12					2
13					2
14					2
15					2
16					3
17					3
18					3
19					4
20					4
21					4
22					5
23					5
24					5
25					5
26					5
27					5
28					5
29					5
30					5
31					6
32					6
33					6
34					6
35					6
36					6
37					7
38					7
39					7
40					7
41					7
42					7
Sheet1

 

[Richard Schollar]

I've had a look at the VBA help in 2003 for the Find method, and under search direction it gives two possibilities: xlNext and xlPrevious. Under Excel97 it also gives these two possibilities. I don't know if it changed for xl2000, but are you sure that xlUp is a valid argument? Try replacing xlUp with xlPrevious using the code I posted and see if it works on your computers (both 2000 and 2003). Otherwise, you'll need to include some version checking code and then modify your code as appropriate.

Richard

 

[nehpets12]

Now works perfectly thank you very much for your time and help