Re: Code needed to find records from bottom up

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 Work***

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
.