To illustrate "p45cal" explanation:

LookIn = xlFormulas ' or xlValues if you want to seek the results of
Formulas also

Set After = Range("A3")

Set cell = Columns(1).Find("LION", After:=After, LookIn:=LookIn, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=True)

If cell Is Nothing Then
MsgBox "Not found"
Else
If cell.Row <= After.Row Then
MsgBox "Found but not in that direction"
Else
MsgBox "Found in " & cell.Address
End If
End If

Mishell


"sunilpatel" wrote in message
...
With activecell "A5", i need to test if "LION" exists before Range("A5")-
searching upwards, then i need to do the same with activecell "A3"

A1="DOG"
A2="CAT"
A3="FISH"
A4="LION"

N% = Columns(1).Find("LION", After:=Range("A5"), LookIn:=xlValues,
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:=True).Row

this yields N% as 4 as expected but

N% = Columns(1).Find("LION", After:=Range("A3"), LookIn:=xlValues,
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:=True).Row

i expected this to yield 0, but N% is still 4

Can someone please explain

Thanks