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.

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

Andy,
As a learning experience I created a small test case of
what you have described. Creating data in the Range
A1:E25 << with Row 1 being the Header rows for the data
and Column A being your Dates (in acsending order - with duplicates)..

I put this is a standard module, and it seems to work.
It's maybe not pretty, but from my limited understand
"it works", so what the heck.. just thought I'd send it
in FWIW..
Jim

Sub Foo()
Dim rg As Range
Dim Lrow As Integer
Dim Rcount As Integer
Dim Mydate As Date
Dim StartCell As String
Dim i As Integer
Lrow = Range("A65536").End(xlUp).Row
Set rg = Range("A1:E" & Lrow)
Rcount = rg.Rows.Count
StartCell = Range("A" & Lrow).Address
Range(StartCell).Select
MsgBox "You have " & Rcount - 1 & " records currently."
Mydate = InputBox("What date are you looking for?")
For i = Rcount To 1 Step -1
If ActiveCell.Value < Mydate Then
GoTo ComeHere
End If
If ActiveCell.Value = Mydate Then
MsgBox "You have a match on row " & ActiveCell.Row
End If
ActiveCell.Offset(-1, 0).Select
Next i

ComeHe
ActiveCell.Offset(1, 0).Select
MsgBox "That's all"
End Sub


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

Hi Jim,

Thanks for the time spent, your code works perfectly.
You and Dave have created a good start to me for today.

Best regards
Andy


"JMay" wrote in message
news:O_Ekf.16756$_k3.2271@dukeread01...
Andy,
As a learning experience I created a small test case of
what you have described. Creating data in the Range
A1:E25 << with Row 1 being the Header rows for the data
and Column A being your Dates (in acsending order - with duplicates)..

I put this is a standard module, and it seems to work.
It's maybe not pretty, but from my limited understand
"it works", so what the heck.. just thought I'd send it
in FWIW..
Jim

Sub Foo()
Dim rg As Range
Dim Lrow As Integer
Dim Rcount As Integer
Dim Mydate As Date
Dim StartCell As String
Dim i As Integer
Lrow = Range("A65536").End(xlUp).Row
Set rg = Range("A1:E" & Lrow)
Rcount = rg.Rows.Count
StartCell = Range("A" & Lrow).Address
Range(StartCell).Select
MsgBox "You have " & Rcount - 1 & " records currently."
Mydate = InputBox("What date are you looking for?")
For i = Rcount To 1 Step -1
If ActiveCell.Value < Mydate Then
GoTo ComeHere
End If
If ActiveCell.Value = Mydate Then
MsgBox "You have a match on row " & ActiveCell.Row
End If
ActiveCell.Offset(-1, 0).Select
Next i

ComeHe
ActiveCell.Offset(1, 0).Select
MsgBox "That's all"
End Sub


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