Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Specific date in Biwwekly Based on date | Excel Discussion (Misc queries) | |||
Julian date - find next highest date/number | Excel Worksheet Functions | |||
Find the date of the coming up Saturday given the current date. | Excel Worksheet Functions | |||
Using variables to make a date and using find method to find that. | Excel Programming | |||
Find date and copy range based on that date | Excel Programming |