Vba Maths Challenge/Problem - Offbeat Weekday Patterns

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
I'm sure this might just be 2 integers working cleverly in tandem, but I'm not 100% it would work for ANY instance...

Scenario:
Userform with 3 pertinent sections -

A) A 'Starting Date' field (Date)
B) A 'Number of Dates' (Long/Byte)
C) 7 Checkboxes, 1 for each day of the week

If a user selects only "Monday", and says "10 Dates", you could very easily just count through a loop adding 7 to the 'Starting Date'

But we need something that means you could Check "Monday" & "Wednesday", enter "6 dates", and wind up with these in cells:

A1 : 03/01/2011
A2 : 05/01/2011
A3 : 10/01/2011
A4 : 12/01/2011
A5 : 17/01/2011
A6 : 19/01/2011

or if the user selected Monday, Tues, Wed, Thur & Fri, and said 20 dates, you'd get:

A1 : 03/01/2011
A2 : 04/01/2011
A3 : 05/01/2011
A4 : 06/01/2011
A5 : 07/01/2011
A6 : 10/01/2011
A7 : 11/01/2011
A8 : 12/01/2011.... etc

basically, populate as many dates as there are 'dates' to enter, based on the pattern set forth by the checkboxes in the userform?

I'm going to put a handle on the form so that the 'Start Date' HAS to be the 'first checked box running Monday - Sun' so you can't enter the 03/01/2011 (A monday) as the 'start date', if the first checkbox to be true is a Wednesday...

Any advice or a nudge in the right direction would be greatly appreciated!

Thanks
C
 

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
Ah. Yes, it looks like it would... I could then pass my checkbox values from True/False into the prescribed pattern for the function...

But For 2003?... We still have a lot of machines on 2003, and this will be running as a userform because it takes the fields in the userform to essentially make a little Database in one of the sheets...

I don't have to initialise it in a loop...necessarily...I don't think...!
 

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
Ah. Yes, it looks like it would... I could then pass my checkbox values from True/False into the prescribed pattern for the function...

But For 2003?... We still have a lot of machines on 2003, and this will be running as a userform because it takes the fields in the userform to essentially make a little Database in one of the sheets...

I don't have to initialise it in a loop...necessarily...I don't think...!
I should also point out that whatever loop I initialise needs to handle anywhere weekday-pattern, not just '2days off' as I think the Workday.intl function does. If I say MTThS, then that has to be a pattern too...

I've started down another path unless someone can suggest something else, I was thinking about adding all the days 'ticked' from the checkboxes to a scripting dictionary, then passing through every day of the week, if it exists, then enter that date forward from the most recent date created, until it runs out of 'number of days' to create?

Thoughts?
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,007
Just "brute force" the solution.

Here's some psuedu code:
Code:
DesiredDays={convert the checkboxes to a 7 element array of true/false values}
nbrdates=clng(me.nbrdates.value)
aDate=cdate(me.startdate.value)
do while nbrdates>0
    if desiredDays(weekday(adate)) then
        nbrdates=nbrdates-1
        write adate to whereever
        end if
    adate=adate+1
    loop
I'm sure this might just be 2 integers working cleverly in tandem, but I'm not 100% it would work for ANY instance...

Scenario:
Userform with 3 pertinent sections -

A) A 'Starting Date' field (Date)
B) A 'Number of Dates' (Long/Byte)
C) 7 Checkboxes, 1 for each day of the week

If a user selects only "Monday", and says "10 Dates", you could very easily just count through a loop adding 7 to the 'Starting Date'

But we need something that means you could Check "Monday" & "Wednesday", enter "6 dates", and wind up with these in cells:

A1 : 03/01/2011
A2 : 05/01/2011
A3 : 10/01/2011
A4 : 12/01/2011
A5 : 17/01/2011
A6 : 19/01/2011

or if the user selected Monday, Tues, Wed, Thur & Fri, and said 20 dates, you'd get:

A1 : 03/01/2011
A2 : 04/01/2011
A3 : 05/01/2011
A4 : 06/01/2011
A5 : 07/01/2011
A6 : 10/01/2011
A7 : 11/01/2011
A8 : 12/01/2011.... etc

basically, populate as many dates as there are 'dates' to enter, based on the pattern set forth by the checkboxes in the userform?

I'm going to put a handle on the form so that the 'Start Date' HAS to be the 'first checked box running Monday - Sun' so you can't enter the 03/01/2011 (A monday) as the 'start date', if the first checkbox to be true is a Wednesday...

Any advice or a nudge in the right direction would be greatly appreciated!

Thanks
C
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
Another way:

Code:
       ----A----- -------B------- -------C-------
   1   Start Date Mon 14 Mar 2011 Mon 14 Mar 2011
   2         nDay              10              10
   3     Workdays 0000011         1010101        
   4                                             
   5              Mon 14 Mar 2011 Tue 15 Mar 2011
   6              Tue 15 Mar 2011 Thu 17 Mar 2011
   7              Wed 16 Mar 2011 Sat 19 Mar 2011
   8              Thu 17 Mar 2011 Tue 22 Mar 2011
   9              Fri 18 Mar 2011 Thu 24 Mar 2011
  10              Mon 21 Mar 2011 Sat 26 Mar 2011
  11              Tue 22 Mar 2011 Tue 29 Mar 2011
  12              Wed 23 Mar 2011 Thu 31 Mar 2011
  13              Thu 24 Mar 2011 Sat 02 Apr 2011
  14              Fri 25 Mar 2011 Tue 05 Apr 2011
  15                                             
  16                                             
  17                                             
  18                                             
  19                                             
  20                                             
  21                                             
  22                                             
  23                                             
  24
The array formula in B5:B24 is

=TRANSPOSE(WorkdaysIntl(B1, B2, B3))

That's copied to C5:C24.

Note that B3 and C3 are strings.

The UDF should (untested) also work in VBA:

Code:
Function WorkdaysIntl(ByVal dBeg As Date, _
                      nDay As Long, _
                      ByVal sWorkDays As String) As Variant
    ' shg 2011
    ' UDF or VBA
 
    ' Returns an array of nDay workdays, starting with the first workday
    ' after dBeg
 
    ' sWorkdays is a 7-character string containing only 1s and 0,
    ' where 0 indicates a workday, starting on Monday
 
    Dim avOut       As Variant
    Dim iDay        As Long
    Dim iPos        As Long
 
    If Len(Replace(sWorkDays, "0", "")) + Len(Replace(sWorkDays, "1", "")) <> 7 Or _
       sWorkDays = "1111111" Or nDay < 1 Then
        WorkdaysIntl = CVErr(xlErrValue)
        Exit Function
    End If
 
    If TypeOf Application.Caller Is Range Then
        With Application.Caller
            If .Rows.Count > 1 And .Columns.Count > 1 Then
                WorkdaysIntl = CVErr(xlErrValue)
            Else
                ReDim avOut(1 To .Cells.Count)
            End If
        End With
    Else
        ReDim avOut(1 To nDay)
    End If
 
    sWorkDays = sWorkDays & sWorkDays
    iPos = InStr(Weekday(dBeg, vbMonday), sWorkDays, "0")
 
    ' get the days
    Do While iDay < nDay
        dBeg = dBeg + iPos - Weekday(dBeg, vbMonday)
        iDay = iDay + 1
        avOut(iDay) = dBeg
        iPos = InStr(Weekday(dBeg, vbMonday) + 1, sWorkDays, "0")
    Loop
 
    ' if UDF, blank-fill any unused portion of array
    For iDay = nDay + 1 To UBound(avOut)
        avOut(iDay) = vbNullString
    Next iDay
 
    WorkdaysIntl = avOut
End Function
 

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
Just "brute force" the solution.

Here's some psuedu code:
Code:
DesiredDays={convert the checkboxes to a 7 element array of true/false values}
nbrdates=clng(me.nbrdates.value)
aDate=cdate(me.startdate.value)
do while nbrdates>0
    if desiredDays(weekday(adate)) then
        nbrdates=nbrdates-1
        write adate to whereever
        end if
    adate=adate+1
    loop
THanks, I was hoping not to have to brute force it, but this works fast enough when I combined with a collection for use of the exists statement.

'Shame that you can't seem to get a 2003/2007 equivalent add-in for the new 2010 functions...
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,007
Why not brute force? Elegance is all nice and good but when the straightforward way is both fast enough and easy enough to understand, it becomes the best way to go, especially when it also means the min. effort testing/debugging the code.

As far as 2010 functions go, if MS retrofitted earlier versions with all the latest bells and whistles, it might seriously cut down on upgrade purchases. {grin}
THanks, I was hoping not to have to brute force it, but this works fast enough when I combined with a collection for use of the exists statement.

'Shame that you can't seem to get a 2003/2007 equivalent add-in for the new 2010 functions...
 

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
Why not brute force? Elegance is all nice and good but when the straightforward way is both fast enough and easy enough to understand, it becomes the best way to go, especially when it also means the min. effort testing/debugging the code.

As far as 2010 functions go, if MS retrofitted earlier versions with all the latest bells and whistles, it might seriously cut down on upgrade purchases. {grin}
Hehe too true... But I'd be much happier to switch to 2010 if I could find where the put everything, and if my GUI interference wasn't thrown off into weird sub-sections of the header menus... sigh... I guess you gotta upgrade sometime...
 

Forum statistics

Threads
1,081,798
Messages
5,361,356
Members
400,628
Latest member
teresajm

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top