Loop & Calculate

[S.H.A.D.O.]

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.

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

 

[jindon]

Don't understnad what you want and you should already have understand the meaning of the code by now...

 

[S.H.A.D.O.]

Thanks for the Reply jindon,

As you know I am New to VBA.
Basically, although there are 6 Numbers for Each Combination, I Need to Cycle through Each 5 Number Combination that can be made from the 24 Numbers Used ( there are 42,504 (=COMBIN(24,5) in Total ), and Count how Many of those are Covered in the Wheel ( 90 in this Case ). Then of those Ninety Combinations Covered, I would like to Count how Many 3 Number Combinations ( there are 2,024 (=COMBIN(24,3) in Total ) are Covered in the Ninety 5 Number Combinations.
I would also like to Calculate the 4 if 5 & 2 if 5 Categories if Possible Please.
I Hope I have Explained this a Bit Clearer Now.

Thanks Very Much in Advance.
All the Best.
SHADO

 

[S.H.A.D.O.]

Any Help, Ideas or Pointers will be Greatly Appreciated.

All the Best.
SHADO

 

[S.H.A.D.O.]

Has anybody got any Suggestions Please.

All the Best.
SHADO

 

[jindon]

S.H.A.D.O.

I suggest you to ask MrExcel to solve this as a business.

Since I wrote several set of codes already on the board as well as though PM, I guess nobody wants to deal with such never-ending project...

 

[S.H.A.D.O.]

Sorry to be a Pain jindon.
I do Appreciate the Time & Effort that you Spent on Previous Replies.
As I said, I am New to VBA.
This is just something I am trying to Achieve for my Own Personal Use, Nothing More.

All the Best.
SHADO