It loops back to the bottom and carries on searching, so if there's only
one LION it will always find it. Try replacing CAT with LION and see
what you get (an interesting way to get rid of the mother-in-law but at
the expense of a habitable house).


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=129975

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

This sets a searchrange as row1 to the activecell.row-1

Sub findup()
what = "lion"
Set msr = Range(Cells(1, 1), Cells(ActiveCell.Row - 1, 1))
On Error GoTo nofind
N% = msr.Find(what, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
MsgBox N%
Exit Sub
nofind: MsgBox "Not found"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

Fix your system's date!

--
Rick (MVP - Excel)


"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

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