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

Hi Dave,

Yes, I'm doing the equivalent of Edit|Find.
Thanks for the codes which work perfectly.
Your detailed explanation is very much appreciated too.

What a nice thing to me to receive early in the morning after waking up ....
switching on the computer.

Best regards
Andy

"Dave Peterson" wrote in message
...
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