Solved

VB Loop - All posible combinations into an array

Posted on 2009-04-09
5
238 Views
Last Modified: 2012-05-06
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

Open in new window

0
Comment
Question by:jamiepryer
  • 3
  • 2
5 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 250 total points
ID: 24113751
I made some assumptions:
- 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)
 

    Dim strToAdd As String

    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"

                possibleCombinations(UBound(possibleCombinations)) = strToAdd

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

            End If

        Next i

    Next j

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

Open in new window

0
 

Author Comment

by:jamiepryer
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"

please..
0
 
LVL 53

Expert Comment

by:Dhaest
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

by:jamiepryer
ID: 24137155
thanks!
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24137221
Glad I could help you...
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now