.find with xlprevious

[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

 

[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

 

[Rick Rothstein]

Fix your system's date!

 

[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