FIND DATE xlLastRow and searchdirection
 

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?

FIND DATE xlLastRow and searchdirection
 

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




On Oct 18, 1:52 pm, "Baine" wrote:
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?

FIND DATE xlLastRow and searchdirection
 

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




On Oct 18, 1:52 pm, "Baine" wrote:
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?

FIND DATE xlLastRow and searchdirection
 

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

On Oct 18, 11:12 pm, "Baine" wrote:
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" wrote in oglegroups.com...



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

On Oct 18, 1:52 pm, "Baine" wrote:
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?- Hide quoted text -- Show quoted text -

FIND DATE xlLastRow and searchdirection
 

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

On Oct 18, 11:12 pm, "Baine" wrote:
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" wrote in
oglegroups.com...



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

On Oct 18, 1:52 pm, "Baine" wrote:
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?- Hide quoted text -- Show quoted text -