# VB Loop - All posible combinations into an array

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
``````
###### Who is Participating?

x

Commented:
- 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 Commented:
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

Commented:
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 Commented:
thanks!
0

Commented: