Selecting All days(dates) in this week and Next

adilucb · Jul 8th 2005

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?

**iwrk4dedpr** · Jul 8th 2005

Re: Selecting All days(dates) in this week and Next

adilucb,

Here's a quick routine I threw together. I just selects the date column but it should work well enough for you to be able to modify it to select the accompanying data set.

Note. Your date cells have values in them which don't translate back into dates. You'll need to ensure that all data in the date columns are VALID dates.

Code

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

Display More

adilucb · Jul 8th 2005

Re: Selecting All days(dates) in this week and Next

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 All days(dates) in this week and Next

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!

**iwrk4dedpr** · Jul 11th 2005

Re: Selecting All days(dates) in this week and Next

adilucb,

Code

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 And Selection.Range("A1").Offset(intRowCount, 0).Row >= 2
        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, 5)).Copy
End Sub

Display More

Give this a whirl and see if it does as you need? Remember information in column A HAS TO BE A VALID DATE.

adilucb · Jul 13th 2005

Re: Selecting All days(dates) in this week and Next

:viking: This is a very simple question, but its one I cant seem to figure out. All I need to do is fidn the code that will allow me to move up, down, etc from a cell without selecting specific cells to move to.

Right now If I want to move right (Im in Cell A1), I have to put in the follwing code: Range("A2").Select
ISNT THERE A WAY I CAN MOVE TO RIGHT, UP, DOWN ETC WITHOUT SPECIFYING A CERTAIN CELL?

Thanks:chase: :chase:

**iwrk4dedpr** · Jul 13th 2005

Re: Selecting All days(dates) in this week and Next

RAnge("A1").offset(0,1)

Activecell.offset(0,1)

Selecting All days(dates) in this week and Next

Participate now!

Share

Users Viewing This Thread