Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find if between dates
hey everyone
I have date/time in colunm A and data through DD and I would like some code that would find all the rows that are between two dates/times and compy them into a different sheet. Any help would be awesome Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find if between dates
Hi -- Try this, it should do the trick. Nothing fancy in here.
This code will copy all data in between rows B through DD the "lowDate" and "highDate" that you specify. It assumes your dates are in Row A. Let me know if you have any questions or need any help. You need to change the "lowDate" and "highDate" below to match whatever dates you want it to look between. I hope this helps, and I hope I've been helpful. Please let me know. Sub copy_data() Dim lowDate As String Dim highDate As String Dim row As Integer Dim skiprow As Integer LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row lowDate = "1/9/2007" highDate = "6/1/2007" row = 0 skiprow = 0 Range("A1:A" & LastRow).Select For Each Cell In Selection If Cell.Value < lowDate Then skiprow = Cell.row + 1 End If If Cell.Value lowDate And Cell.Value < highDate Then row = Cell.row End If Next Range("B" & skiprow & ":DD" & row).Select Selection.Copy Sheets.Add ActiveSheet.Paste Range("A1").Select End Sub -- Best of Luck!, Chris Van Nuys Become more effective and efficient: Drive your business career by mastering Excel! " wrote: hey everyone I have date/time in colunm A and data through DD and I would like some code that would find all the rows that are between two dates/times and compy them into a different sheet. Any help would be awesome Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find if between dates
Hi!
Try and the macro below as an second suggestion. It's more flexible and integrated. '---------------------8<---------------------------- Sub CopyDataRowsByDate() Dim dtmLowDate As Date Dim dtmHiDate As Date Dim rngCell As Range Dim strSheetName As String Dim wsTargetSheet As Worksheet On Error Resume Next Sheets(1).Activate 'Specify your data Worksheet InputLowDate: dtmLowDate = DateValue(InputBox("Specify LowDate")) If dtmLowDate 0 Then InputHiDate: dtmHiDate = DateValue(InputBox("Specify HiDate")) If dtmHiDate 0 Then InputSheetName: strSheetName = InputBox("Specify the name of target worksheet") Set wsTargetSheet = Worksheets(strSheetName) If Not wsTargetSheet Is Nothing Then For Each rngCell In Range("A1:A" & Range("A:A").SpecialCells(11).Row).Cells If rngCell dtmLowDate And rngCell < dtmHiDate Then Range("A" & rngCell.Row, "D" & rngCell.Row).Copy _ Destination:=wsTargetSheet _ .Range("A" & Rows.Count).End(xlUp).Offset(1) End If Next rngCell Else If MsgBox("Invalid worksheet name.", _ vbCritical + vbOKCancel, "Worksheet name") = vbOK Then GoTo InputSheetName End If End If Else If MsgBox("Invalid date string.", _ vbCritical + vbOKCancel, "Hi date value") = vbOK Then GoTo InputHiDate End If End If Else If MsgBox("Invalid date string.", _ vbCritical + vbOKCancel, "Low date value") = vbOK Then GoTo InputLowDate End If End If End Sub '---------------------8<---------------------------- Regards, John Ο χρήστης "Chris Van Nuys" *γγραψε: Hi -- Try this, it should do the trick. Nothing fancy in here. This code will copy all data in between rows B through DD the "lowDate" and "highDate" that you specify. It assumes your dates are in Row A. Let me know if you have any questions or need any help. You need to change the "lowDate" and "highDate" below to match whatever dates you want it to look between. I hope this helps, and I hope I've been helpful. Please let me know. Sub copy_data() Dim lowDate As String Dim highDate As String Dim row As Integer Dim skiprow As Integer LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row lowDate = "1/9/2007" highDate = "6/1/2007" row = 0 skiprow = 0 Range("A1:A" & LastRow).Select For Each Cell In Selection If Cell.Value < lowDate Then skiprow = Cell.row + 1 End If If Cell.Value lowDate And Cell.Value < highDate Then row = Cell.row End If Next Range("B" & skiprow & ":DD" & row).Select Selection.Copy Sheets.Add ActiveSheet.Paste Range("A1").Select End Sub -- Best of Luck!, Chris Van Nuys Become more effective and efficient: Drive your business career by mastering Excel! " wrote: hey everyone I have date/time in colunm A and data through DD and I would like some code that would find all the rows that are between two dates/times and compy them into a different sheet. Any help would be awesome Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find if between dates
Chris,
Great stuff!! I changed low date and high date to date rather then string and its working awesome. Only one problem, if there are no rows with the date then it doesn't work. Is there anyway to stop this? What I want to do with this now is the following: If there are is data that fall in the dates then send an email if not end sub without error and run the next macro. Thanks so much for the help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare 2 tables of dates to find the preceding dates | Excel Worksheet Functions | |||
FIND and dates | Excel Worksheet Functions | |||
For Next does not find dates? | Excel Programming | |||
How do I find the earliest dates in a range of dates? | Excel Worksheet Functions | |||
Using Find with dates | Excel Programming |