vba code differance excel 2000 excel 2003 xlup

nehpets12

Active Member
Joined
Feb 22, 2002
Messages
453
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 ?



Code:
    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]
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Code:
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

Active Member
Joined
Feb 22, 2002
Messages
453
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
BCDEF
151
21
31
41
52
62
72
82
92
102
112
122
132
142
152
163
173
183
194
204
214
225
235
245
255
265
275
285
295
305
316
326
336
346
356
366
377
387
397
407
417
427
Sheet1
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Forum statistics

Threads
1,141,718
Messages
5,708,070
Members
421,543
Latest member
SGM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top