Solved

# VB Loop - All posible combinations into an array

Posted on 2009-04-09
238 Views
Hi,
Im trying to write some results into an array for working out all possible combinations of days, weeks and months, between 2 dates.

For exmaple, between 1st Jan and the 20th jan, i want to know all combinations of days, weeks, months you could have

Total Days = 21
Day = 1
Week = 7
Month = 31

Combinations to an array, should be something like:
1 = 21day, 0 Weeks, 0 Months
2 = 14day, 1 week,0 months
3 = 7day, 2 week,0 months
4 = 0day, 3 week,0 months
``````sub CalCombinations()

StartDate = "1/01/2009"

EndDate = "31/01/2009"

TotalDays = DateDiff("d", StartDate, EndDate) + 1 'add 1 day to cover for the start day

TotalFullWeeks = DateDiff("w", StartDate, EndDate)

TotalFullMonths = DateDiff("m", StartDate, EndDate)

End sub
``````
0
Question by:jamiepryer
• 3
• 2

LVL 53

Accepted Solution

Dhaest earned 250 total points
ID: 24113751
- A month has 4 weeks
- A month has 30 days (average)

``````    Dim possibleCombinations() As String

ReDim possibleCombinations(0)

StartDate = "1/01/2009"

EndDate = "28/02/2009"

totalDays = DateDiff("d", StartDate, EndDate) + 1 'add 1 day to cover for the start day

TotalFullWeeks = DateDiff("w", StartDate, EndDate)

totalFullMonths = DateDiff("m", StartDate, EndDate)

For j = 0 To totalFullMonths

For i = 0 To (TotalFullWeeks + (totalFullMonths * 4))

If (totalDays - ((j * 30) + (i * 7))) > 0 Then

strToAdd = (totalDays - ((j * 30) + (i * 7))) & " days, " & i & " Weeks, " & j & " Months"

ReDim Preserve possibleCombinations(UBound(possibleCombinations) + 1) As String

End If

Next i

Next j

ReDim Preserve possibleCombinations(UBound(possibleCombinations) - 1) As String
``````
0

Author Comment

ID: 24136360
thanks, that works really well
can you just talk me through your thinking for the

"    For I = 0 To (TotalFullWeeks + (totalFullMonths * 4))"

and

"If (totalDays - ((j * 30) + (I * 7))) > 0 Then"

0

LVL 53

Expert Comment

ID: 24136628
1. For I = 0 To (TotalFullWeeks + (totalFullMonths * 4))"
We need to loop for each week (but a month contains 4 weeks, that's why you have
"number of weeks" + ("number of months" * 4 = number of weeks for these months)

2. If (totalDays - ((j * 30) + (I * 7))) > 0 Then
j represents a month (so x-monts * 30 days to have the number of days
l represents a week (so x-weeks * 7 days) to have the number of days

Totaldays is a number that gives a an number of days between start and end.
When we have for example: 1 week and 1 day -> we'll have also the possibility to have 8 days
If we don't use formula above, we'll get the following result
1 week - 1 days
0 week - 8 days
0 week - 1 days
The last is incorrect off-course
0

Author Comment

ID: 24137155
thanks!
0

LVL 53

Expert Comment

ID: 24137221
0

## Featured Post

Unlike scripting languages such as C# where a semi-colon is used to indicate the end of a command, Microsoft's VBScript language relies on line breaks to determine when a command begins and ends. As you can imagine, this quickly results in messy cod…
I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…