Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and copy rows based on dates in two columns
Hi
Sheet01 contains details of club members. Now I need a makro to find members who were active on a certain date (or a perid), and copy those rows to Sheet02 Sheet01 has EnterDate in Col 1, ExitDate in Col 2 From a userform on Sheet03 i plan to get two dates from user: LastExitDate and FirstEnterDate. I suppose logic should be: ExitDate empty or = LastExitDate (dont want those who left earlier) EnterDate <= FirstEnterDate (dont want those who entered after) Writing a macro that solves this is beyond my skills - any help is very appreciated Thank you! ulf |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and copy rows based on dates in two columns
You need tot modify the constants StartDate and EndDate as required. You
also may need to changge the sheet names "Sheet1" and "Sheet2". Sub movemembers() Const StartDate As Date = "4/7/07" Const EndDate As Date = "7/4/07" With Sheets("Sheet2") Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row End With Sh2RowCount = Sh2LastRow + 1 With Sheets("Sheet1") Sh1Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row For Sh1RowCount = 1 To Sh1Lastrow EnterDate = .Range("A" & Sh1RowCount) ExitDate = .Range("B" & Sh1RowCount) If IsDate(EnterDate) And IsDate(ExitDate) Then If (EnterDate <= StartDate) And _ (ExitDate = EndDate) Then .Rows(Sh1RowCount).Copy Destination:= _ Sheets("Sheet2").Rows(Sh2RowCount) End If End If Next Sh1RowCount End With End Sub "ulfb" wrote: Hi Sheet01 contains details of club members. Now I need a makro to find members who were active on a certain date (or a perid), and copy those rows to Sheet02 Sheet01 has EnterDate in Col 1, ExitDate in Col 2 From a userform on Sheet03 i plan to get two dates from user: LastExitDate and FirstEnterDate. I suppose logic should be: ExitDate empty or = LastExitDate (dont want those who left earlier) EnterDate <= FirstEnterDate (dont want those who entered after) Writing a macro that solves this is beyond my skills - any help is very appreciated Thank you! ulf |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and copy rows based on dates in two columns
Thank you - that took me a long way.
One thing remains: all members have enter dates - but only some have exit dates - these cells are empty So I guess this has to be modified? If IsDate(EnterDate) And IsDate(ExitDate) Then If (EnterDate <= StartDate) And _ (ExitDate = EndDate) Then Regards ulf "Joel" wrote: You need tot modify the constants StartDate and EndDate as required. You also may need to changge the sheet names "Sheet1" and "Sheet2". Sub movemembers() Const StartDate As Date = "4/7/07" Const EndDate As Date = "7/4/07" With Sheets("Sheet2") Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row End With Sh2RowCount = Sh2LastRow + 1 With Sheets("Sheet1") Sh1Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row For Sh1RowCount = 1 To Sh1Lastrow EnterDate = .Range("A" & Sh1RowCount) ExitDate = .Range("B" & Sh1RowCount) If IsDate(EnterDate) And IsDate(ExitDate) Then If (EnterDate <= StartDate) And _ (ExitDate = EndDate) Then .Rows(Sh1RowCount).Copy Destination:= _ Sheets("Sheet2").Rows(Sh2RowCount) End If End If Next Sh1RowCount End With End Sub "ulfb" wrote: Hi Sheet01 contains details of club members. Now I need a makro to find members who were active on a certain date (or a perid), and copy those rows to Sheet02 Sheet01 has EnterDate in Col 1, ExitDate in Col 2 From a userform on Sheet03 i plan to get two dates from user: LastExitDate and FirstEnterDate. I suppose logic should be: ExitDate empty or = LastExitDate (dont want those who left earlier) EnterDate <= FirstEnterDate (dont want those who entered after) Writing a macro that solves this is beyond my skills - any help is very appreciated Thank you! ulf |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and copy rows based on dates in two columns
I don't think the code needs to be modified based on your original instructions. Empty cells will not meet the isdate test and will not be included. "ulfb" wrote: Thank you - that took me a long way. One thing remains: all members have enter dates - but only some have exit dates - these cells are empty So I guess this has to be modified? If IsDate(EnterDate) And IsDate(ExitDate) Then If (EnterDate <= StartDate) And _ (ExitDate = EndDate) Then Regards ulf "Joel" wrote: You need tot modify the constants StartDate and EndDate as required. You also may need to changge the sheet names "Sheet1" and "Sheet2". Sub movemembers() Const StartDate As Date = "4/7/07" Const EndDate As Date = "7/4/07" With Sheets("Sheet2") Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row End With Sh2RowCount = Sh2LastRow + 1 With Sheets("Sheet1") Sh1Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row For Sh1RowCount = 1 To Sh1Lastrow EnterDate = .Range("A" & Sh1RowCount) ExitDate = .Range("B" & Sh1RowCount) If IsDate(EnterDate) And IsDate(ExitDate) Then If (EnterDate <= StartDate) And _ (ExitDate = EndDate) Then .Rows(Sh1RowCount).Copy Destination:= _ Sheets("Sheet2").Rows(Sh2RowCount) End If End If Next Sh1RowCount End With End Sub "ulfb" wrote: Hi Sheet01 contains details of club members. Now I need a makro to find members who were active on a certain date (or a perid), and copy those rows to Sheet02 Sheet01 has EnterDate in Col 1, ExitDate in Col 2 From a userform on Sheet03 i plan to get two dates from user: LastExitDate and FirstEnterDate. I suppose logic should be: ExitDate empty or = LastExitDate (dont want those who left earlier) EnterDate <= FirstEnterDate (dont want those who entered after) Writing a macro that solves this is beyond my skills - any help is very appreciated Thank you! ulf |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Macro- Insert rows based on dates and copy info from that row | Excel Discussion (Misc queries) | |||
How do I find and copy rows based on specific criteria? | New Users to Excel | |||
Find Duplicates Rows Based Own Multiple Columns | Excel Discussion (Misc queries) | |||
Help with Copy based on dates code! | Excel Programming | |||
Macro simplifying - copy rows to worksheets based on values in 2 different columns | Excel Programming |