Loop & Calculate

P

Paul Black

Hi Everyone,

Six Numbers are drawn.
The code below cycles through a range of Six Number Combinations and
produces a value of those covered for Five if Five is matched.
It basically tests for how many Five Number Combinations are covered in
the Fifteen Six Number Combinations ( Cells G13:L27 ) with Numbers One
to Twenty Four.
There are 42,504 Five Number Combinations of Twenty Four Numbers (
=COMBIN(24,5) ).
The code below produces a value of those that are covered out of the
42,504 Combinations ( Ninety, which is correct ).
How can the code below be adapted to ALSO calculate ( value to go in
Cell O17 ) how many Combinations are covered for Three if Five Please.
I was Told for the Interpretation of the 3 if 5 Category that you Need
to Cycle through ALL 5 Number Combinations that can be Constructed from
the Total Numbers Used in the Wheel ( 24 in this Case ). So if the
Wheel Contains "x" Unique Numbers, you Need to Cycle through ALL 5
Number Combinations from those "x" Numbers. Then you Need to Scan the
Wheel for Each 5 Number Combination Produced and Compare it with Each
Line in the Wheel to see if that Line Matches the 5 Number Combination
in *EXACTLY* 3 Numbers. If it does, then that Combination of 3 if 5 is
Covered and Added to the Total and there is NO Need to Continue to
Check for that Particular Combination Any Further. You then go onto the
Next Combination to Check and so on Until ALL Combinations have been
Cycled through and Checked with the Wheel.
I hope I have explained this clearly enough.
Here is the code :-

Sub test_for_5()
Dim a, dic As Object
Set dic = CreateObject("scripting.dictionary")
a = Range("G13").CurrentRegion.Value
For i = 1 To UBound(a, 1)
For ii = 1 To 2
For iii = ii + 1 To 3
For iv = iii + 1 To 4
For v = iv + 1 To 5
For vi = v + 1 To 6
z = a(i, ii) & "," & a(i, iii) & a(i, iv) &
a(i, v) & a(i, vi)
If Not dic.exists(z) Then
dic.Add z, Nothing
n = n + 1
End If
Next vi, v, iv, iii, ii, i
Set dic = Nothing
Range("O16") = n
End Sub

Thanks in Advance.
All the Best.
Paul
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top