Code needed to find records from bottom up

[Andy]

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]

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

[JMay]

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

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


"Andy" <[email protected]> wrote in message
news:OdiWebN%[email protected]...
> 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.
>

[Andy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> 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
>
> ComeHere:
> ActiveCell.Offset(1, 0).Select
> MsgBox "That's all"
> End Sub
>
>
> "Andy" <[email protected]> wrote in message
> news:OdiWebN%[email protected]...
>> 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.
>>
>
>