# Loop & Calculate

#### S.H.A.D.O.

##### Well-known Member
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.
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
n = n + 1
End If
Next vi, v, iv, iii, ii, i
Set dic = Nothing
Range("O16") = n
End Sub``````
All the Best.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Don't understnad what you want and you should already have understand the meaning of the code by now...

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.

All the Best.

Any Help, Ideas or Pointers will be Greatly Appreciated.

All the Best.

Has anybody got any Suggestions Please.

All the Best.

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

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.

Replies
1
Views
174
Replies
4
Views
220
Replies
0
Views
273
Replies
2
Views
341
Replies
1
Views
310

1,203,673
Messages
6,056,677
Members
444,881
Latest member
Stu2407

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back