Search from the bottom of a Range up

[Guest]

Is there an efficient way to searching from the bottom of a range up?

I have this:

for counter = 1 to 200
if cells(200-counter, 1) = "blah") then...
endif
next counter

 

[Trevor Shuttleworth]

for counter = 200 to 1 Step -1
if cells(200-counter, 1) = "blah") then...
end if
next counter

 

[Dave Peterson]

for counter = 200 to 1 step -1
if cells(counter, 1) = "blah") then...
endif
next counter

or more convoluted:

for counter = 1 to 200
if cells(201-counter, 1) = "blah") then...
endif
next counter

 

[Tim Childs]

Hi

if it is an Excel range, cannot you use the built-in functionality?
Something like:
Selection.Find(What:="blah", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False).Activate
i.e. use the SearchDirection parameter

hth (and is correct !)

Tim

 

[Dave Peterson]

If I wanted to find the last value in a range (say a column), I'd start in the
first (top) cell and look up (xlprevious) from there. I wouldn't start with the
activecell.

dim FoundCell as range
with worksheets("sheet9999")
with .range("a1:A9999")
set foundcell = .Find(What:="blah", _
After:=.cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
end with
end with

if foundcell is nothing then
msgbox "not found"
else
msgbox foundcell.value & " was found " & foundcell.address
end if

====
And if I wanted to find the first entry in that column, I'd start in the last
cell and look down (xlnext).

dim FoundCell as range
with worksheets("sheet9999")
with .range("a1:A9999")
set foundcell = .Find(What:="blah", _
After:=.cells(.cells.count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
end with
end with

=======
And if I had to search for more entries, I'd look in VBA's help for .findnext().

 

[Tim Childs]

Dave

me too! but it would have taken me too long to puzzle out the VB code!!

bw

Tim