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

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" wrote:

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

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

On Oct 31, 10:49 am, Joel <J...@discussions.microsoft.com> wrote:
> 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" wrote:
> > 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- Hide quoted text -
>
> - Show quoted text -

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" wrote:

> 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
>
> On Oct 31, 10:49 am, Joel <J...@discussions.microsoft.com> wrote:
> > 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" wrote:
> > > 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- Hide quoted text -
> >
> > - Show quoted text -
>
>
>

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

On Oct 31, 12:11 pm, Joel <J...@discussions.microsoft.com> wrote:
> 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" wrote:
> > 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
>
> > On Oct 31, 10:49 am, Joel <J...@discussions.microsoft.com> wrote:
> > > 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" wrote:
> > > > 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- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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" wrote:

> 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
>
> On Oct 31, 12:11 pm, Joel <J...@discussions.microsoft.com> wrote:
> > 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" wrote:
> > > 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
> >
> > > On Oct 31, 10:49 am, Joel <J...@discussions.microsoft.com> wrote:
> > > > 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" wrote:
> > > > > 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

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

On Oct 31, 2:17 pm, Joel <J...@discussions.microsoft.com> wrote:
> 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" wrote:
> > 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
>
> > On Oct 31, 12:11 pm, Joel <J...@discussions.microsoft.com> wrote:
> > > 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" wrote:
> > > > 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
>
> > > > On Oct 31, 10:49 am, Joel <J...@discussions.microsoft.com> wrote:
> > > > > 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" wrote:
> > > > > > 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
>
> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -

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


"Paul Black" wrote:

> 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
>
> On Oct 31, 2:17 pm, Joel <J...@discussions.microsoft.com> wrote:
> > 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" wrote:
> > > 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
> >
> > > On Oct 31, 12:11 pm, Joel <J...@discussions.microsoft.com> wrote:
> > > > 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" wrote:
> > > > > 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
> >
> > > > > On Oct 31, 10:49 am, Joel <J...@discussions.microsoft.com> wrote:
> > > > > > 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

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


"Joel" wrote:

> 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" wrote:
>
> > 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
> >
> > On Oct 31, 12:11 pm, Joel <J...@discussions.microsoft.com> wrote:
> > > 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" wrote:
> > > > 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
> > >
> > > > On Oct 31, 10:49 am, Joel <J...@discussions.microsoft.com> wrote:
> > > > > 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

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

On Oct 31, 3:01 pm, Joel <J...@discussions.microsoft.com> wrote:
> 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
>
>
>
> "Joel" wrote:
> > 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" wrote:
>
> > > 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
>
> > > On Oct 31, 12:11 pm, Joel <J...@discussions.microsoft.com> wrote:
> > > > 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" wrote:
> > > > > 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
>
> > > > > On Oct 31, 10:49 am, Joel <J...@discussions.microsoft.com> wrote:
> > > > > > 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- Hide quoted text -
>
> - Show quoted text -