PDA

View Full Version : Solved: Excel 2000 to 2003 compatibility issue with .Find



Wizard
12-01-2008, 02:15 PM
I have a spreadsheet with data for multiple company branches. We are splitting the data into separate reports for each branch.

When creating a branch report, we first copy the worksheet to a new workbook, find the last entry for that branch, then delete any rows below that.

To find the last row of data for any given branch, we have always used this
(DestCol is the column number, Destination is the branch name):
Columns(DestCol).Find(What:=Destination, After:=Cells(65536, DestCol), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlUp, MatchCase:=False).Select

and it has always worked fine under Excel 2000.

One member of our group just got a new laptop that has Excel 2003... and now that same line of code (which still works fine under 2000) doesn't find the last row of data for that branch, it finds the first! :wot

Can anyone offer any insight to why this may be happening?

Thx
Wizard

GTO
12-01-2008, 04:16 PM
Greetings,

Try changing the xlSearchDirection.

Dim DestCol As Long
Dim Destination As String
Destination = "Example Branch"
DestCol = 2

Columns(DestCol).Find(What:=Destination, After:=Cells(65536, DestCol), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Select

I'm not where I can get to 2000, but seems I recall (seems as in I may well be wrong) that xlPrevious works in 2000 as well... maybe?

Mark

Wizard
12-02-2008, 09:04 AM
Well we're not going to find out if that works or not... they downgraded her Office version back to 2000, so the question is now moot.

I'll mark it solved just the same... after all, it is solved, just not via VBA code.

Thanks just the same!