Are you doing the equivalent of Edit|Find in code?

If you are, then there's a parameter in the .find command that tells excel which
way to look:

SearchDirection:=xlNext
becomes
SearchDirection:=xlPrevious

And one nice way to start looking from the bottom up is to start in the
firstcell, but use xlprevious:

Option Explicit
Sub Testme()

Dim FoundCell As Range
Dim RngToLook As Range
Dim DateToLookFor As Date
Dim FirstAddress As String
Dim wks As Worksheet

DateToLookFor = DateSerial(2005, 12, 1)

Set wks = Worksheets("sheet1")

With wks
Set RngToLook = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With RngToLook
Set FoundCell = .Cells.Find(what:=DateToLookFor, _
After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox DateToLookFor & " wasn't found"
Else
FirstAddress = FoundCell.Address
Do
MsgBox FoundCell.Address(0, 0)
Set FoundCell = .FindPrevious(FoundCell)
Loop While FoundCell.Address < FirstAddress
End If
End With

End Sub

If you wanted to start at the top and look down, you could use the lastcell and
xlnext:


With RngToLook
Set FoundCell = .Cells.Find(what:=DateToLookFor, _
After:=.Cells(.cells.count), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox DateToLookFor & " wasn't found"
Else
FirstAddress = FoundCell.Address
Do
MsgBox FoundCell.Address(0, 0)
Set FoundCell = .Find(FoundCell)
Loop While FoundCell.Address < FirstAddress
End If
End With

=====
And you didn't ask for this, but if you're doing Edit|Find manually, you can
shift-click on the Find button (in that dialog) and it searches in reverse
order. Neat trick, huh?

Andy wrote:

Hi,

I have a database that is arranged in chronological order sorted by date in
ascending order, I want to find records from bottom of the database up.
More, if I need to find the next matching record, I want to find next
again from the bottom up that is one row up from the first found record.

Can someone provide code to do that. Thanks

Ps What I have been doing is to sort the database by date by decending order
first then do a normal find.

--

Dave Peterson