Selecting rows by date for 14 day periods

adilucb · Jul 7th 2005

I have data from a calendar in my excel spreadsheet, I want to be able to select any date located in the first Column A, and then run a macro which will find the date 2 weeks ahead, copy the data in between and paste in a new sheet or new file.

The problem is the date 2 weeks ahead must be a weekday, becasue weekend and holiday info is missing. If the date 14 days ahead is missing I want the macro to go to the previous day and try to find it, if it is also missing then go back 1 more day...until it find a date which is actually in the calendar data.

Thanks for the help, i appreciate it!

adilucb · Jul 7th 2005

Re: Selecting rows by date for 14 day periods

I have attached a workbook which contains data which is similar to the data I will be working with later.

As can be seen Column A conatins all the dates, What i want to do is select any cell in Column A and be able to copy all the rows down from the Selected Date to 2 weeks forward (14 days ahead). I want to copy all the data in between and paste it into a new sheet.

I want to copy from Start Date to End date, 14 day period. Whatever cell I click on I want the macro to copy all the data between Start and End. Sometimes End date wont be in the data, there are gaps for weekend and holidays then I want the macro to check End Date -1, go back one day and check, if there is still no date go back another day until a end date can be set. Then Copy from Start Date to end Date and past in a new sheet.

spog00 · Jul 7th 2005

Re: Selecting rows by date for 14 day periods

You can try this, I think it works, but may need tweaking in some places. All results are pasted in sheet2

Hope this helps

Alastair

adilucb · Jul 7th 2005

Re: Selecting rows by date for 14 day periods

Thanks it seems to be working great, except the Start date is being matched to the end date based on the date (1/01/05) and the Time, I want to only use the date to signify the end date. How can I change this so it works even is the time is different but the date is the same?

spog00 · Jul 7th 2005

Re: Selecting rows by date for 14 day periods

That is where I was having problems. I tried a very long winded method of cutting the dates to only dates (ignore times) but this then treated the result as text which led to all sorts of problems. Managed to work my way through most of these and thenr realised I would get problems going over the month boundaries, and gave up that thought process...

My best suggestion is to try and split the columns, not ideal I know, but I couldn't find an easy solution around it.

IF there is an easy way to convert text into a date format this can *probably* be done relatively easily, but as of yet I know of no such command.

Sorry, I guess this really doesn't help much in terms of getting a solution...

Alastair

adilucb · Jul 8th 2005

Re: Selecting rows by date for 14 day periods

I got a hold of some script which will convert the date nad time to just a date, now I just have to work on fitting it into the macro and I should be set.

Dim myDate, ConvertDate As Date

myDate = Sheets("sheet1").Range("A1:A" & end_row)
ConvertDate = Month(myDate) & "/" & Day(myDate) & "/" & Year(myDate)

rbrhodes · Jul 8th 2005

Re: Selecting rows by date for 14 day periods

Hi,

I built this for you. Worked fine in my copy of your example.

Code

Sub DateFind()


Dim FirstRow As Long, LastRow As Long
Dim StartDate As Date, EndDate As Date
Dim ws1 As Worksheet, ws2 As Worksheet
Dim PutInRow As Long, DateFoundRow As Long


    Set ws1 = Sheets(1)
    Set ws2 = Sheets(2)
    
    PutInRow = ws2.Range("a65536").End(xlUp).Row
    ws1.Activate
    FirstRow = ActiveCell.Row
    LastRow = Range("a65536").End(xlUp).Row + 1
    StartDate = (Left(ActiveCell, 9))
    EndDate = StartDate + 14
    
    On Error GoTo notfound
    
    'finds last instance of EndDate or EndDate minus 1 to closest date
    Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
       xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False _
       , SearchFormat:=False).Activate
    DateFoundRow = ActiveCell.Row
    Range("A" & FirstRow, "F" & DateFoundRow).Copy Destination:=ws2.Cells(PutInRow, 1)
    Application.ScreenUpdating = True
    Exit Sub
notfound:
    EndDate = EndDate - 1
    If EndDate > StartDate Then
        Resume
    End If
    
    MsgBox ("No date found")


End Sub

Display More

Cheers,

dr

adilucb · Jul 8th 2005

Re: Selecting rows by date for 14 day periods

Ive been working on the code, and all your help has been great thanks, but now I have a new kind of problem.

HELP NEW PROBLEM . . .
I realized I need to be able to copy the data for the week I select and the following week, the last code I was workign on would give me 14 days no matter what the start date.

What i need to create is a smart macro which recognizes which day of the week it is then goes back a few days to include that intial entire week and then moves forward to include the following week.

So lets say I start by click on a date which is a Thursday, i need the macro to select all the days behind thursday (monday, tues, weds & STOP AT SATURAY), then i need it to look forward to teh next week and select all days for the following week Monday Through Friday.

I know this sounds complicated, thats why I cam to the experts, any thoughts guys?

adilucb · Jul 8th 2005

Re: Selecting rows by date for 14 day periods

rbrhodes your script above didnt work, Also i have new type of problem I am facign its described above as well.
All the help has been great thanks, but now I have a new kind of problem.
I have an excel sheet which has a column A with dates, and toher column of data.
I need to be able to copy the data for the week I am in depending on which Day I select and also copy the following week, the last code I was workign on would give me 14 days ahead from a given start date.

What i need to create is a smart macro which recognizes which day of the week it is then goes back a few days to include that intial entire week and then moves forward to include the following week.

So lets say I start by clicking on a date which is a Thursday, i need the macro to select all the days behind thursday (monday, tues, weds & STOP AT SATURAY), then i need it to look forward to teh next week and select all days for the following week Monday Through Friday.

Example)
If I select 7/8/05 which is a Friday, I want the macro to go back a few dates all the way to begenning of week at Tuesday 7/05/05. Then I want it to also look forward and copy the days in the following week which are tues 7/21 through Fri 7/15

I know this sounds complicated, thats why I cam to the experts, any thoughts guys?

adilucb · Jul 8th 2005

Re: Selecting rows by date for 14 day periods

Private Sub CommandButton1_Click()

' Local Variables
Dim dteEnd As Date
Dim dteStart As Date
Dim intRowCount As Integer

' Verify selection contains a valid date
If Not IsDate(Selection.Range("A1")) Then Exit Sub

' Set date selection range
dteStart = DateValue(DateAdd("d", -Weekday(Selection.Range("A1")) + 1, Selection.Range("A1")))
dteEnd = DateAdd("d", 13, dteStart)

' Find size of data and select
intRowCount = 0
Do While Selection.Range("A1").Offset(intRowCount, 0) >= dteStart
intRowCount = intRowCount - 1
Loop
Selection.Range("A1").Offset(intRowCount + 1, 0).Select
intRowCount = 1
Do While Selection.Range("A1").Offset(intRowCount + 1, 0) <= dteEnd
intRowCount = intRowCount + 1
Loop
Range(Selection, Selection.Range("A1").Offset(intRowCount, 0)).Select
End Sub

That good seems to be working fine, THANKS!

there are still a few lil things I need to figure out, I get an error right now when the macro tries to find the start of a week and the date is not give, example maybe the data doesnt go that far back, Is there a way I can program it to back back as many days as possible???

so if its friday selected, then the macro goes back as many days as possible to collect that week, maybe in that week monday tuesday were off so macro collects weds thurs and fri.

Also Can you adjust the macro so it will copy all the columns not jut row A and then paste this content in Sheet two, One set of dates after another ...
Your help is much appreciated!

adilucb · Jul 11th 2005

Re: Selecting rows by date for 14 day periods

I get an error right now when the macro tries to find the start of a week and the date is not give, example maybe the data doesnt go that far back, Is there a way I can program it to back back as many days as possible???

so if its friday selected, then the macro goes back as many days as possible to collect that week, maybe in that week monday tuesday were off so macro collects weds thurs and fri.

Also Can you adjust the macro so it will copy all the columns not jut row A and then paste this content in Sheet two, One set of dates after another ...
Your help is much appreciated!

spog00 · Jul 11th 2005

Re: Selecting rows by date for 14 day periods

Double Post!!

http://www.ozgrid.com/forum/sh…d.php?p=181530#post181530

Alastair

adilucb · Jul 11th 2005

Re: Selecting rows by date for 14 day periods

i apologize for the double post, I'm a new user and wasnt sure whether ppl helping me would go back to the original string or the new one

Selecting rows by date for 14 day periods

Participate now!

Share

Users Viewing This Thread