Solved: vlookup:Search from bottom row instead of top row

[TheAnswer]

Set subValue = Workbooks("bbca volume report [vba]2010_may_team.xls").Worksheets(sheetActive).Columns("A"). _
Find(What:=subArea, After:=Columns("A").Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)[/vba]

With this, i am able to search for value in a column from top row to bottom row. But is there a way to search from the bottom row to the top row instead?

[austenr]

This will return the last match, assuming your lookup value is in A1 and your return results are contained in column D....change as necessary.

[VBA]=LOOKUP(2,1/($C$1:$C$5201=A1),$D$1:$D$5201)[/VBA]

[kroz]

not tested:

[vba]
A = Range("A:A"). _
Find(What:=subArea, Before:=Range("A65550"), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
[/vba]

[TheAnswer]

Thanks to both of you
Kroz: It worked!!