Count Specific Total Numbers from a Set

[Paul Black]

Hi everyone,

I am trying to count the total number of combinations that contain
0,1,2,3,4,5 and 6 Prime Numbers in a 649 Lotto.
I am having trouble putting the code together.
I am trying to use a Select Case construct. I am trying to do it this
way because there are other sets of numbers that I would like to test,
this way, all I will have to do is change the numbers in the Select
Case to those that I want.
This is what I have so far :-

Option Explicit
Option Base 1

Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer

Sub Prime()
Dim nVal(6) As Double
Dim n As Integer

For n = 0 To 6
nVal(n) = 0
Next n

For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49

If nVal = 0 Then nVal(0) = nVal(0) + 1
If nVal = 1 Then nVal(1) = nVal(1) + 1
If nVal = 2 Then nVal(2) = nVal(2) + 1
If nVal = 3 Then nVal(3) = nVal(3) + 1
If nVal = 4 Then nVal(4) = nVal(4) + 1
If nVal = 5 Then nVal(5) = nVal(5) + 1
If nVal = 6 Then nVal(6) = nVal(6) + 1

Next F
Next E
Next D
Next C
Next B
Next A

Range("A1").Select

For n = 0 To 6
ActiveCell.Offset(0, 0).Value = n
ActiveCell.Offset(0, 1).Value = Format(nVal(n), "#,0")
ActiveCell.Offset(1, 0).Select
Next n

End Sub

Private Function nVal() As Integer

Select Case A
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = nVal + 1
End Select
Select Case B
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = nVal + 1
End Select
Select Case C
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = nVal + 1
End Select
Select Case D
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = nVal + 1
End Select
Select Case E
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = nVal + 1
End Select
Select Case F
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = nVal + 1
End Select

End Function

Any help will be greatly appreciated.
Thanks in Advance.
All the Best.
Paul

 

[Joel]

Option Base 1 says arrays start at 1 not 0. You also need another array.
One to handle the results and one to contain the input numbers 0 to 5. You
may want to change the numbers ffrom 0 to 5 to 1 to 6.


Option Explicit
Option Base 1

Dim A As Integer, B As Integer, C As Integer, D As Integer
Dim E As Integer, F As Integer

Sub Prime()
Dim nVal As Variant
Dim Data As Variant
Dim n As Integer

Data = Array(0, 1, 2, 3, 4, 5)
nVal = Array(0, 0, 0, 0, 0, 0)

For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49

For n = 1 To UBound(Data)
If A = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If B = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If C = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If D = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If E = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If F = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
Next n

Next F
Next E
Next D
Next C
Next B
Next A

Range("A1").Select

For n = 1 To 6
Range("A" & n).Value = n
Range("B" & n).Value = Format(nVal(n), "#,0")
Next n

End Sub

Private Function nVal(inVal As Integer) As Integer

Select Case A
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case B
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case C
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case D
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case E
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case F
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select

End Function

 

[Paul Black]

Thanks for the reply Joel,

Actually, I want combinations that contain 0,1,2,3,4,5 and 6 Prime
Numbers, so I don't need the Option Base 1 in this instance.
I tried adapting your code to accomodate this but to no avail.

Thanks in Advance.
All the Best.
Paul

 

[Joel]

You then need to change A to start at 0. Option base is independant from
data starting at 0. The option base only concenrs itself with the first
index of an arrray either being 0 or 1. The data items are in the array DATA
and not an index.

Option Explicit
Option Base 1

Dim A As Integer, B As Integer, C As Integer, D As Integer
Dim E As Integer, F As Integer

Sub Prime()
Dim nVal As Variant
Dim Data As Variant
Dim n As Integer

Data = Array(0, 1, 2, 3, 4, 5)
nVal = Array(0, 0, 0, 0, 0, 0)

For A = 0 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49

For n = 1 To UBound(Data)
If A = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If B = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If C = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If D = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If E = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If F = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
Next n

Next F
Next E
Next D
Next C
Next B
Next A

Range("A1").Select

For n = 1 To 6
Range("A" & n).Value = n
Range("B" & n).Value = Format(nVal(n), "#,0")
Next n

End Sub

Private Function nVal(inVal As Integer) As Integer

Select Case A
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case B
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case C
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case D
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case E
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case F
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select

 

[Paul Black]

Sorry to be a pain Joel,

I really want the "A" to start at 1 To 44.
Also, the program does NOT produce the total combinations with NO
Prime Numbers.

I calculated the totals using formulas and I got :-
0 = 1,344,904
1 = 4,173,840
2 = 4,869,480
3 = 2,722,720
4 = 765,765
5 = 102,102
6 = 5,005

Total combinations = 13,983,816

Thanks in Advance.
All the Best.
Paul

 

[Joel]

I get the same answers as you

Option Explicit
Option Base 0

Dim A As Integer, B As Integer, C As Integer, D As Integer
Dim E As Integer, F As Integer

Sub Prime()
Dim nVal As Variant
Dim n As Integer
Dim numberPrimes As Integer

nVal = Array(0, 0, 0, 0, 0, 0, 0)

For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
numberPrimes = nValF()
nVal(numberPrimes) = nVal(numberPrimes) + 1
Next F
Next E
Next D
Next C
Next B
Next A

Range("A1").Select

For n = 1 To 7
Range("A" & n).Value = n - 1
Range("B" & n).Value = Format(nVal(n - 1), "#,0")
Next n

End Sub

Private Function nValF() As Integer
Dim nVal As Integer

nVal = 0
Select Case A
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nValF = nValF + 1
End Select
Select Case B
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nValF = nValF + 1
End Select
Select Case C
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nValF = nValF + 1
End Select
Select Case D
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nValF = nValF + 1
End Select
Select Case E
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nValF = nValF + 1
End Select
Select Case F
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nValF = nValF + 1
End Select

End Function

 

[Paul Black]

Brilliant Joel,

It works great, thank you.
Am I right in saying that you are using "n - 1" because of the "Option
Base 0"?. I thought that it just assumed that unless otherwise told.
I noticed that you used an Array. Could ...

For Each item In Array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41,
43, 47)
Prime(Item) = SOMETHING or OTHER
Next
... be used for the 7 totals instead of the Select Case construct for
instance?.
If so, how can this be incorporated into the code?.
Would this make the code more compact?.

Thanks in Advance.
All the Best.
Paul

 

[Joel]

I used an array instead of using a for loop to initialize the array. Both
are equivalent.

You don't have to use the option explicit for 0, but it doesn't hurt.
Option Base 1 was incorrect.

I used n-1 for output to cells only. You can't have a row number of 0. The
array goes from 0 to 6, the row count goes from 1 to 7.

The function nValF return a value from 0 to 6. In the main function I just
take this value and put it in the array without using the n - 1. The array
index 0 contains the results when there are no primes. Index 1 contains the
count of 1 prime, etc....

numberPrimes = nValF()
nVal(numberPrimes) = nVal(numberPrimes) + 1

 

[Joel]

Here is a different version of the code

Option Explicit
Option Base 0

Sub Prime()
Dim nVal As Variant
Dim n As Integer
Dim numberPrimes As Integer
Dim number(6) As Variant
Dim A, B, C, D, E, F As Integer

nVal = Array(0, 0, 0, 0, 0, 0, 0)

For A = 1 To 44
number(0) = A
For B = number(0) + 1 To 45
number(1) = B
For C = number(1) + 1 To 46
number(2) = C
For D = number(2) + 1 To 47
number(3) = D
For E = number(3) + 1 To 48
number(4) = E
For F = number(4) + 1 To 49
number(5) = F
numberPrimes = nValF(number())
nVal(numberPrimes) = nVal(numberPrimes) + 1
Next F
Next E
Next D
Next C
Next B
Next A

Range("A1").Select

For n = 1 To 7
Range("A" & n).Value = n - 1
Range("B" & n).Value = Format(nVal(n - 1), "#,0")
Next n

End Sub

Private Function nValF(ByRef number() As Variant) As Integer
Dim n As Integer

nValF = 0
For n = 0 To 5
Select Case number(n)
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nValF = nValF + 1
End Select
Next n
End Function

 

[Paul Black]

Hi Joel,

I ran both of the programs and the first one took 23 seconds to run
and the different version took 1 minute 5 seconds to run.
I like the idea of using just one set of numbers in the Select Case
construct like you used in the different version but it just seems to
slow things right down.
I wondered how long it would take if I did NOT use a Function for the
Select Case construct and moved the Select Case cnstruct into the Sub
itself.
I have mucked about for a few hours but I can't seem to get the Sub to
produce the correct information. Am I right in saying that I will
definately need to use a Function for this?.

Thanks in Advance.
All the Best.
Paul

 

[Joel]

I'm laughting! I thought about eliminating the function after I made the
last change, but I already got the code working and didn't want to make
another change. here is the code in 1 function. The time would be faster if
I could put an array in a for loop but it gives an error

---------------------------
This doesn't work. would reduce execution time
from
For A = 1 To 44
number(0) = A
to
For n(0) = 1 To 44
number(0) = n(0)
-------------------------

Option Explicit
Option Base 0

Sub Prime()
Dim nVal As Variant
Dim n As Integer
Dim numberPrimes As Integer
Dim number(6) As Variant
Dim A, B, C, D, E, F As Integer

nVal = Array(0, 0, 0, 0, 0, 0, 0)

For A = 1 To 44
number(0) = A
For B = number(0) + 1 To 45
number(1) = B
For C = number(1) + 1 To 46
number(2) = C
For D = number(2) + 1 To 47
number(3) = D
For E = number(3) + 1 To 48
number(4) = E
For F = number(4) + 1 To 49
number(5) = F

numberPrimes = 0
For n = 0 To 5
Select Case number(n)
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
numberPrimes = numberPrimes + 1
End Select
Next n
nVal(numberPrimes) = nVal(numberPrimes) + 1
Next F
Next E
Next D
Next C
Next B
Next A

Range("A1").Select

For n = 1 To 7
Range("A" & n).Value = n - 1
Range("B" & n).Value = Format(nVal(n - 1), "#,0")
Next n

End Sub

 

[Paul Black]

Hi Joel,

Do you mean something like ...

For Each item In Array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37,
41,
43, 47)
Prime(Item) = SOMETHING or OTHER
Next

.... when you say ...

"The time would be faster if I could put an array in a for loop"

.... in your previous post.
Your original code works perfectly, and I don't know why I thought
that a Function was the right way to go. I suppose it was the fact
that I have quite a few number sets I would like to calculate and that
I really only wanted to change the numbers in the set and then run the
program.

Thanks in Advance.
All the Best.
Paul

 

[Paul Black]

Hi Joel,

Thinking about it, would it be better to just include ALL the Select
Cases in the original Sub?.

Thanks in Advance.
All the Best.
Paul

 

[Joel]

What I meant was the fewer lines of code that you have to execute tthe faster
the program will run. In the example I posted I forgot to remove one line of
code. I would like to use the "to" code below because in one instruction I
could put the numbers 1 to 44 into an arrray. The first example it takes two
instructions. the second apoproach would be faster because it take one less
line of code.

For A = 1 To 44
number(0) = A
to
For n(0) = 1 To 44

This is the version I would expect to run the fastest

Option Explicit
Option Base 0
Sub Prime()

Dim A As Integer, B As Integer, C As Integer, D As Integer
Dim E As Integer, F As Integer
Dim nVal As Variant
Dim n As Integer
Dim numberPrimes As Integer

nVal = Array(0, 0, 0, 0, 0, 0, 0)

For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
numberPrimes = 0
Select Case A
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
numberPrimes = numberPrimes + 1
End Select
Select Case B
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
numberPrimes = numberPrimes + 1
End Select
Select Case C
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
numberPrimes = numberPrimes + 1
End Select
Select Case D
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
numberPrimes = numberPrimes + 1
End Select
Select Case E
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
numberPrimes = numberPrimes + 1
End Select
Select Case F
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
numberPrimes = numberPrimes + 1
End Select
nVal(numberPrimes) = nVal(numberPrimes) + 1

Next F
Next E
Next D
Next C
Next B
Next A

Range("A1").Select

For n = 1 To 7
Range("A" & n).Value = n - 1
Range("B" & n).Value = Format(nVal(n - 1), "#,0")
Next n

End Sub

 

[Dana DeLouis]

Hi. My code was similar, but my timing was also about 20 seconds.
I thought I could make a small change for speed, but my timing was the same.
As F goes from 7 to 49, and E goes from 5 to 48, etc we notice that A
doesn't change much.
Yet, we are always testing if A,(B, C etc) are prime on every loop. As
each variable changed, I tested if it is prime so I wouldn't keep
unnecessarily testing each variable.
However, my timing remained the same at around 20 seconds. I really thought
it would be faster, but it's not. Hmmm. I don't know why it wasn't faster.

However, certain problems like this can be rearranged to cut the timing down
to about 2 seconds via the following idea.

Notice that we are doing 83 million Prime tests.
83,902,896 (=COMBIN(49,6)*6)

Here, we do the test only 49 times.

Sub Prime()
Dim A As Long, B As Long, C As Long
Dim D As Long, E As Long, F As Long
Dim R As Long
Dim Tme As Double

' Are Prime ? (Yes = 1, No = 0)
Dim n(1 To 49) As Long
' Hold solution count
Dim NPrime(0 To 6) As Long

Tme = Timer

' Do Once Here
For R = 1 To 49
n(R) = PrimeQ(R)
Next R

For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
R = n(A) + n(B) + n(C) + n(D) + n(E) + n(F)
NPrime(R) = NPrime(R) + 1
Next F
Next E
Next D
Next C
Next B
Next A

' Display Solution:
For R = 0 To 6
Cells(R + 1, 1) = R
Cells(R + 1, 2) = Format(NPrime(R), "#,0")
Next R

Debug.Print Timer - Tme
End Sub

Private Function PrimeQ(n) As Long
Select Case n
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
PrimeQ = 1
Case Else
PrimeQ = 0
End Select
End Function

My timing was 2 seconds.
Always an interesting subject. :>)

 

[Paul Black]

Joel,
Your code works perfectly, thank you for your time and help. It runs
in about 23 seconds.

Hi Dana,
I hope you are keeping well.
Your code ran in about 3 seconds.
Is there any way your "Private Function PrimeQ(n) As Long" code can be
incorporated into the Sub itself. I don't know if this will make it
even quicker.

Thanks in Advance.
All the Best
Paul

 

[Paul Black]

Joel,
Your code works perfectly, thank you for your time and help. It runs
in about 23 seconds.

Hi Dana,
I hope you are keeping well.
Your code ran in about 3 seconds.
Is there any way your "Private Function PrimeQ(n) As Long" code can
be
incorporated into the Sub itself. I don't know if this will make it
even quicker.
Dana, it would also be nice to have a total underneath the ...
Cells(R + 1, 2) = Format(NPrime(R), "#,0")
.... please.


Thanks in Advance.
All the Best
Paul

 

[Paul Black]

Hi Dana,

I have got it to produce the Total combinations output for
NPrime(R) ...

Cells(R + 1, 2) = Format(NPrime(R), "#,0")

.... by ADDING two extra lines of code. I think the following is
correct? :-

Sub Prime()
Dim A As Long, B As Long, C As Long
Dim D As Long, E As Long, F As Long
Dim R As Long
Dim Tme As Double

' Are Prime ? (Yes = 1, No = 0)
Dim n(1 To 49) As Long
' Hold solution count
Dim NPrime(0 To 6) As Long

Tme = Timer

' Do Once Here
For R = 1 To 49
n(R) = PrimeQ(R)
Next R

For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
R = n(A) + n(B) + n(C) + n(D) + n(E) + n(F)
NPrime(R) = NPrime(R) + 1
Total = Total + 1 ' <<<<<<<<< ADDED >>>>>>>>>
Next F
Next E
Next D
Next C
Next B
Next A

' Display Solution:
For R = 0 To 6
Cells(R + 1, 1) = R
Cells(R + 1, 2) = Format(NPrime(R), "#,0")
Cells(R + 2, 2) = Format(Total, "#,0") ' <<<<<<<<< ADDED >>>>>>>>>
Next R

Debug.Print Timer - Tme
End Sub

Private Function PrimeQ(n) As Long
Select Case n
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
PrimeQ = 1
Case Else
PrimeQ = 0
End Select
End Function

I just can't seem to be able to incorporate the Select Case construct
into the Sub itself.

Thanks in Advance.
All the Best
Paul

 

[Dana DeLouis]

Hi Paul. Here are a couple of ideas.

Total = Total + 1 ' <<<<<<<<< ADDED >>>>>>>>>

Notice that we are running the Total command above 13 million times.
However, the Total is also the SUM of the 7 numbers in NPrime (0 to 6)
I would skip that Total line, and just add the 7 values in the Solution
array.

In the code below, notice that you are displaying the Total on each loop.
You probably meant to do it only once at the end.

For R = 0 To 6
Cells(R + 1, 1) = R
Cells(R + 1, 2) = Format(NPrime(R), "#,0")
Cells(R + 2, 2) = Format(Total, "#,0") ' <<<<<<<<< ADDED >>>>>>>>>
Next R

Technique: Select the line of code with " For R = 0 To 6" in it from above,
and hit the F9 key. This will place a Break-Point on this line.
Now, run the code. The code will stop as it enters the loop. Now, hit the
F8 key, and step thru the code. You will see the logic error now.

I just can't seem to be able to incorporate the Select Case construct
into the Sub itself.

I factored out the Sub in case you wanted to run other tests. Suppose you
wanted a count of the number of Even or Odd numbers, or some other idea.
This way, it would be easy to adjust the code. We are only looping 49
times, so I don't think this is a real factor on the timing of the program.
Anyway, here are some ideas:


Sub Prime()
Dim A As Long, B As Long, C As Long
Dim D As Long, E As Long, F As Long
Dim R As Long
Dim S As String
Dim Tme As Double
Dim Total As Long

' Are Prime ? (Yes = 1, No = 0)
Dim n(1 To 49) As Long
' Hold solution count
Dim NPrime(0 To 6) As Long

Tme = Timer

' Do Once Here
For R = 1 To 49
Select Case R
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
n(R) = 1
Case Else
n(R) = 0
End Select
Next R

For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
R = n(A) + n(B) + n(C) + n(D) + n(E) + n(F)
NPrime(R) = NPrime(R) + 1
Next F
Next E
Next D
Next C
Next B
Next A

' Display Solution:

For R = 0 To 6
Cells(R + 1, 1) = R
Cells(R + 1, 2) = Format(NPrime(R), "#,0")
Total = Total + NPrime(R)
Next R

S = Format(Total, "#,0")
Cells(R + 1, 2) = S

'// Stop timer before MsgBox
Debug.Print Timer - Tme

'// Check
If Total = WorksheetFunction.Combin(49, 6) Then
MsgBox "Total is correct: " & S
Else
MsgBox "Error in Total: " & S
End If

End Sub

 

[Paul Black]

Brilliant Dana, thanks VERY much.

Your code will satisfy several other tests I want to produce very
nicely that use a single set of numbers.
I like the idea of checking that all combinations are present, very
neat.
I would like to do two more tests, mainly count the number of Odd &
Even numbers, but that involves two scenarios, one for Odd & the other
for Even :-

0 Odd + 6 Even = 134,596
1 Odd + 5 Even = 1,062,600
2 Odd + 4 Even = 3,187,800
3 Odd + 3 Even = 4,655,200
4 Odd + 2 Even = 3,491,400
5 Odd + 1 Even = 1,275,120
6 Odd + 0 Even = 177,100
Totals > = 1,3983,816

I would like to do this by using the actual Odd & Even numbers in the
code itself, because there are other tests I want to produce that also
require using two seperate sets of numbers. That way, I can just enter
the new sets of numbers.

Thanks in Advance.
All the Best.
Paul