FIND DATE xlLastRow and searchdirection

[Baine]

Sub FindDates()
On Error GoTo errorHandler
Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer

startDate = InputBox("Enter the Start Date: (mm/dd/yy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (mm/dd/yy)")
If stopDate = "" Then End
startDate = Format(startDate, "mm/dd/yy")
stopDate = Format(stopDate, "mm/dd/yy")
startRow = Worksheets("MASTER").Columns("C").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("MASTER").Columns("C").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("MASTER").Range("B" & startRow & ":AZ" & stopRow).Copy _
Destination:=Worksheets("Sheet3").Range("A1")
End
errorHandler:
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 48
End Sub

My date is in column C. I sometimes have more than one with the same date.
I select the dates with the input box and it gets all of the rows I want
except if there is more than one with the same stopDate. The sheet has been
sorted on the date column. I thought if I could search from the bottom up
it would solve my problem. Could I get some help with the code to do a
seartch from the bottom up?

 

[WhytheQ]

I think you may need to use the FindNext method! There is a good
example in the online help which might get you started:

'=======================================
This example finds all cells in the range A1:A500 that contain the
value 2 and changes their values to 5.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
=======================================

.....so the above example loops down to the last instance of 2 and
changes it to a 5: the way the above loops down through the values to
find the last instance of 2 is the bit I reckon you might find useful.

Rgds
J

 

[Baine]

Rather than start with a totally different method of doing this I would
prefer to add the code to what I already have and find the last row of data
and change the direction of the search. I tried a for next loop with an if
then to check for more than one of the same stop date. The reason it didn't
work is when I looked at the next cell in the date column it returned
"3/22/06" instead of "03/22/06". In this same module, in a different sub I
am using Function LastRow(sh As Worksheet). I would like to use this in
this sub.
Thanks

 

[WhytheQ]

Hi Baine,

I don't believe you can change the direction of Find: please let me
know if you come across a method to do so.

I was playing with the above code to see how difficult it would be to
build in my suggestion, and it would probably look something like:

Sub FindDates()

Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer

On Error GoTo errorHandler

startDate = InputBox("Enter the Start Date: (mm/dd/yy)")
If startDate = "" Then End

stopDate = InputBox("Enter the Stop Date: (mm/dd/yy)")
If stopDate = "" Then End

startDate = Format(startDate, "mm/dd/yy")
stopDate = Format(stopDate, "mm/dd/yy")

startRow = Worksheets("MASTER").Columns("C").Find(startDate, _
LookIn:=xlValues, LookAt:=xlWhole).Row

'=========replacement code===================================
'stopRow = Worksheets("MASTER").Columns("C").Find(stopDate, _
' LookIn:=xlValues, lookat:=xlWhole).Row
With Worksheets("MASTER").Columns("C")
stopRow = .Find(What:=stopDate, LookIn:=xlValues,
LookAt:=xlWhole).Row
'need to find the last occurance of the reference
Do Until Worksheets("MASTER").Cells(stopRow, 3)(2, 1) <>
stopDate
stopRow = .FindNext(stopDate).Row
Loop
End With
============================================================

Worksheets("MASTER").Range("B" & startRow & ":AZ" & stopRow).Copy _
Destination:=Worksheets("Sheet3").Range("A1")

End
errorHandler:
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 48

End Sub

.....haven't tested it !
What is the code behingd the function you mentioned LastRow(sh As
Worksheet) ?

Sorry I've not been too much help,
Rgds
J

 

[Baine]

Thanks again for your help. It didn't help. The loop doesn't work because
the cells(stopRow, 3) returns 3/22/06 instead of 03/22/06. Your loop would
probably work if I were searching for an invoice number or part number.
Earlier I had to change the cell format of my date column to get the
original code to work and the cells command doesn't like the date format.
Catch-22! A frustrating situation in which one is trapped by contradictory
regulations or conditions.

 

[WhytheQ]

....an invoice number is exactly the situation I used that loop for !
Have you tried changing the variable declarations at the start of the
routine to Date?

J

 

[Baine]

Hay, I just got it fixed. I never could get the last row function to work
in this sub although I am using it in this same module. I put a little loop
to find the last date in the column and then I changed to xlPrevious.
startRow = Worksheets("MASTER").Columns("C").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
i = 2
While Cells(i, 3).Value <> "" And i < 65535
i = i + 1
Wend
i = i - 1
stopRow = Worksheets("MASTER").Columns("C").Find(stopDate, _
LookIn:=xlValues, SearchDirection:=xlPrevious, lookat:=xlWhole).Row
I will look at changing the variable to date to see what effect that might
have.
Thanks.