Consulting

Results 1 to 13 of 13

Thread: Master broken into week 1, week2, etc...

  1. #1
    VBAX Regular belly0fdesir's Avatar
    Joined
    Jan 2006
    Location
    Inland Empire
    Posts
    36
    Location

    Master broken into week 1, week2, etc...

    I have an Access database and I'm going to have the user select a month from a dropdown on a form in the database, have a query create a new table based on that criteria, then a macro will call on a spreadsheet, which will have a database query to the newly created table... the table/master sheet will have a list of order numbers and dates and other data....

    what I don't know how to do and need some direction is I need that master sheet to then be broken into 5 other sheets when the workbook is opened... sorting the data into Week 1, Week 2, Week 3, Week 4 and Week 5.

    Can someone help me out or point me in the right direction?

    Thank you in advance to anyone who replies

  2. #2
    Hi

    This function may be of help.
    I assumed that 1st day of the week is Monday, you may want to change that. Also, in the line highlighted in red, you might have to change the whole expression, because of regional settings. (I'm not sure about the latter.)
    [vba]Function WhichWeek(Cell As Range) As Long
    Dim Ref As Date, RefWeekDay As Long

    Ref = CDate(Year(Cell) & "." & Month(Cell) & ".1")
    RefWeekDay = Weekday(Ref, vbMonday)

    WhichWeek = (Day(Cell) + RefWeekDay - 2) \ 7 + 1
    End Function
    [/vba]
    By the way, a month can span over 6 weeks even, like this July, so count on 6 new sheets instead of 5.
    See the attached example.

    Jimmy
    -------------------------------------------------
    The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.

  3. #3
    VBAX Regular belly0fdesir's Avatar
    Joined
    Jan 2006
    Location
    Inland Empire
    Posts
    36
    Location
    sweet! this will definitely help... thank you... i'm still not sure how i'm going to do this, but at least i have a function with which I can test if my data meets the criteria... thank you.

  4. #4
    One possible way is
    [vba]
    Function WhichWeek(Cell As Range) As Long
    Dim Ref As Date, RefWeekDay As Long

    Ref = CDate(Year(Cell) & "." & Month(Cell) & ".1")
    RefWeekDay = Weekday(Ref, vbMonday)

    WhichWeek = (Day(Cell) + RefWeekDay - 2) \ 7 + 1
    End Function

    Sub SortByWeek()
    Dim MS As Worksheet, DS As Worksheet
    Dim MR As Range, DR As Range, c As Range
    Dim WeekNo As Long

    'defining Master Sheet and Master Range
    Set MS = ThisWorkbook.Sheets("Master")
    Set MR = MS.Range("A1", MS.Range("A" & MS.Rows.Count).End(xlUp))

    'looping through Master Range (cells that contain dates)
    For Each c In MR.Cells
    'obtaining week number
    WeekNo = WhichWeek(c)

    On Error GoTo CreateSheet
    'defining destination sheet, if it doesn't exist, create it
    Set DS = ThisWorkbook.Sheets("Week" & WeekNo)
    'defining destination range
    Set DR = DS.Range("A" & DS.Rows.Count).End(xlUp).Offset(1)
    'copying a row from Master Sheet to Destination Sheet
    c.EntireRow.Copy DR
    Next

    Exit Sub

    CreateSheet:
    ThisWorkbook.Worksheets.Add
    Set DS = ActiveSheet
    DS.Name = "Week" & WeekNo
    Resume Next
    End Sub
    [/vba] I am assuming that the sheet with your data is called "Master", and that dates you want to sort by are located in column "A". Without knowing the layout of your table/sheet, I can not do much more.

    Jimmy
    -------------------------------------------------
    The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.

  5. #5
    VBAX Regular belly0fdesir's Avatar
    Joined
    Jan 2006
    Location
    Inland Empire
    Posts
    36
    Location
    Works exactly as I want it to... there are a few things I need to change to accomodate my specific needs, but this is absolutely what I was looking for.
    Consider this one solved!!!

    Thank you for both your answers! I learned a lot!

    I'm wondering tho, in this line:

    Ref = CDate(Year(Cell) & "." & Month(Cell) & ".1")

    I changed this so that it would work for me... I just used (Cell)... but I'm wondering why your code reads liket his... is this some conversion that you need to do in Europe or something?

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Okay, you asked for it.

    Yeah, the rest of the world has to coerce dates because stupid MS and stupid VBA decided that even though the majority of the world doesn't use US style dates, and even though we have regional settings on our computers that can tell each application what settings we use, including NORMAL date settings, MS and VBA know best and inflict US style dates in VBA regardless.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Regular belly0fdesir's Avatar
    Joined
    Jan 2006
    Location
    Inland Empire
    Posts
    36
    Location
    ^^ thank you for that explanation and sorry for MS's arrogance :/



    the code is getting confused at this point:

    WhichWeek = (Day(Cell) + RefWeekDay - 2) / 7 + 1

    it's placing July 5th in Week 2... I'm trying to break it down and understand how this formula is supposed to work, but I'm havin' troubles with it... can you explain to me?

    The Day of (Cell), which is 5, plus RefWeekDay, which is 5 = 10, minus 2 makes 8, divided by 7 makes 1, plus 1 means Week 2... If I were to take out the " + 1" it would give me what I want, but I don't know if that would work in all cases... and the whole formula just feels strange to me... I'm probably not understanding the formula enough for it to be that easy .

  8. #8
    It all comes down to what you mean by "week".
    For me, a week starts with Monday and ends with Sunday, so July 1st, which is Sunday, is the only day in 1st week of July. 2nd of July is Monday, it belongs to the next week, which is the 2nd week of July. See it all (kind of) charted below.
    July, 2007.      
                Mo   Tue   Wed   Thu   Fri   Sat   Sun       
    Week 1                                          1
    Week 2      2     3     4     5     6     7     8       
    Week 3      9    10    11    12    13    14    15       
    Week 4     16    17    18    19    20    21    22       
    Week 5     23    24    25    26    27    28    29       
    Week 6     30    31     
    So what's wrong with placing July 5th in Week 2? How should it be, in your opinion?

    Jimmy
    -------------------------------------------------
    The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.

  9. #9
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    It's not / but \. You want a whole number.

  10. #10
    VBAX Regular belly0fdesir's Avatar
    Joined
    Jan 2006
    Location
    Inland Empire
    Posts
    36
    Location
    @jimmythehand: ah... i see... I'm in the states, so our weeks start on Sunday... but I did change it to vbSunday to accomodate this...

    I've changed a few other things to accomodate all of my particular needs, but none of the things I've changed should cause it to react as such... i've attached a simplified copy of the workbook to this post.

    Normally, the worksheet is filled with formulas that are linked to another sheet that is a query to a table in an access database. All of those things have been removed from the version I've uploaded, but, as expected, the code still acts in the same manner. Can you take a look?

    @Charlize: wow... so that's what that does... that's going to come in hand thanks for clearing that up for me!

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    I don't think that you properly implemented Jimmy's Whichweek function

    [vba]

    Function WhichWeek(Cell As Range) As Long
    Dim Ref As Date, RefWeekDay As Long

    Ref = CDate(Year(Cell.Value) & "/" & Month(Cell.Value) & "/01")
    RefWeekDay = Weekday(Ref, vbSunday)

    WhichWeek = (Day(Cell.Value) + RefWeekDay - 2) \ 7 + 1
    End Function
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    Okay, I will try to explain, meanwhile changing my algorithm to US system.
    See the attached workbook for reference.

    Let's assume that the current month begins with Sunday, Week #1. This assumption holds for July, so it's a perfect month for us right now.
    If I want to calculate which week a day is in, I need to use something like this formula:
    Week = INT(DayOfMonth /7)
    This is shown in column E. You can see that the 7th day belongs to the 2nd week, which is wrong, and is corrected in column F with the following formula:
    Week = INT((DayOfMonth-1) /7)
    Compare the yellow cells.
    Now, if I want the 1st week to get an index of 1 instead zero, I have to add 1 to the result. See column G, and the following formula
    Week = INT((DayOfMonth-1) /7)+1
    This is, so far, all happy and works well, but once we reach August, it will give wrong results. See red cells in column G.
    5th Aug, which is Sunday, should belong to Week 2, instead of Week 1.
    This happens because August doesn't start with Sunday.
    August starts with Wednesday, which is 4th day of the week, so it is, sort of, a 3 days offset to 1st day of the week (Sunday). This means, the whole month is shifted by 3 days. This is corrected in column H:
    Week = INT((DayOfMonth-1+3) /7)+1
    But column H is only valid for August, as September starts with Saturday, which is a 6 days offset. So, in order to get a formula that is valid for all and each months, you need a reference day in each month. The most simple choice is 1st day of the month.
    So the formula is:
    Week = INT((DayOfMonth-1 + Weekday(1st day of month) -1) /7)+1
    'which is the same as 
    Week = INT((DayOfMonth + Weekday(1st day of month) -2) /7)+1
    And it was here that I noticed Bob's post, and decided that the rest should be self-evident. (With some thinking, probably.)

    Jimmy
    -------------------------------------------------
    The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.

  13. #13
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location

    try weeknum() and sumproduct

    Not sure if this will work in your formulas.
    Try Weeknum()
    it is part of the add-in for Analysis ToolPak

    I use it where there is a date in column A,
    Column A is a named range "jobdate"
    and another column in same row put =weeknum(A1) and fill down
    ' this is a helper column
    this helper row is named wknum

    in another formula I refernece the result with sumproduct

    =sumproduct(--(data1=value),--(data2=value2))

    my actual code looks like this:

    =SUMPRODUCT(--(Pass="x"),--(JobDate=WEEKNUM(WkStart)))

    pass, jobdate and wkstart are named ranges

    data1 is the range you are looking in
    value 1 is what you limit the results by
    if you have dats in column A from jan to aug
    - the valu could be any month that falls in that range...
    The true result will then compare the second criteria

    Or you can just use sumproduct without the helper column
    You have to name a cell as StartDate and EndDate
    set up your sheet with columns where you enter a date,
    - and name the range "JobDate"

    in a cell you want the sum or result type:
    =sumproduct(--(JobDate=>startdate),--(jobdate=<stopdate),--(check=value))

    I have a few posts with this sumproduct and weeknum()
    look up under mperrah
    lucas, mdmcillop and charlize have all helped with this as well.
    Mark
    Last edited by mperrah; 07-07-2007 at 02:17 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •