.find with xlprevious

P

p45cal

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).
 
M

Mishell

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
 
D

Don Guillett

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
 
S

sunilpatel

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
 
Top