# Vba Maths Challenge/Problem - Offbeat Weekday Patterns

#### ClimoC

##### Well-known Member
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
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
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
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)
do while nbrdates>0
nbrdates=nbrdates-1
end if
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
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
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)
do while nbrdates>0
nbrdates=nbrdates-1
end if
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
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
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...

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

### 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...