|
Count Specific Total Numbers from a Set
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 |
Count Specific Total Numbers from a Set
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 |
Count Specific Total Numbers from a Set
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 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 - |
Count Specific Total Numbers from a Set
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 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 - |
Count Specific Total Numbers from a Set
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 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 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 - |
Count Specific Total Numbers from a Set
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 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 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 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 - |
Count Specific Total Numbers from a Set
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 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 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 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 |
Count Specific Total Numbers from a Set
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 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 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 |
Count Specific Total Numbers from a Set
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 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 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 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 - |
Count Specific Total Numbers from a Set
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" wrote: 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 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 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 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 |
Count Specific Total Numbers from a Set
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 On Nov 1, 1:44 pm, Joel wrote: 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" wrote: 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 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 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 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 ... read more »- Hide quoted text - - Show quoted text - |
Count Specific Total Numbers from a Set
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 On Nov 1, 4:01 pm, Paul Black wrote: 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 On Nov 1, 1:44 pm, Joel wrote: 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" wrote: 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 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 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 ... read more »- Hide quoted text - - Show quoted text - |
Count Specific Total Numbers from a Set
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 "Paul Black" wrote: 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 On Nov 1, 1:44 pm, Joel wrote: 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" wrote: 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 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 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 |
Count Specific Total Numbers from a Set
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. :) -- Dana DeLouis "Joel" wrote in message ... 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 "Paul Black" wrote: 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 On Nov 1, 1:44 pm, Joel wrote: 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" wrote: 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 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 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 |
Count Specific Total Numbers from a Set
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 On Nov 2, 8:32 am, "Dana DeLouis" wrote: 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. :) -- Dana DeLouis "Joel" wrote in message ... 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 "Paul Black" wrote: 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 On Nov 1, 1:44 pm, Joel wrote: 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" wrote: 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 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 ... read more »- Hide quoted text - - Show quoted text - |
Count Specific Total Numbers from a Set
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 On Nov 2, 8:32 am, "Dana DeLouis" wrote: 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. :) -- Dana DeLouis "Joel" wrote in message ... 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 "Paul Black" wrote: 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 On Nov 1, 1:44 pm, Joel wrote: 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" wrote: 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 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 ... read more »- Hide quoted text - - Show quoted text - |
Count Specific Total Numbers from a Set
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 On Nov 2, 11:57 am, Paul Black wrote: 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 On Nov 2, 8:32 am, "Dana DeLouis" wrote: 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. :) -- Dana DeLouis "Joel" wrote in message ... 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 "Paul Black" wrote: 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 On Nov 1, 1:44 pm, Joel wrote: 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" wrote: 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 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 - ... read more »- Hide quoted text - - Show quoted text - |
Count Specific Total Numbers from a Set
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 -- Dana DeLouis <snip |
Count Specific Total Numbers from a Set
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 On Nov 2, 3:40 pm, "Dana DeLouis" wrote: 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 -- Dana DeLouis <snip |
Count Specific Total Numbers from a Set
Brilliant Dana, thanks VERY much for the detailed explanation and
code. 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. Could you please explain what the variables "R", "n", "NPrime", "PrimeQ" & "S" actually do please. It appears that the variable "R" means one thing at the beginning of the code and something else further down. 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. The results should be as follows :- 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 On Nov 2, 3:40 pm, "Dana DeLouis" wrote: HiPaul. 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 -- Dana DeLouis <snip |
Count Specific Total Numbers from a Set
Instead of Select Case, and looping 49 times, I suppose we could loop 15
times. As you can see, there are many different ideas one can use. The idea here is that the Array is initialized with zero anyway, and we are just setting a few of them to 1. Dim P As Variant ' Do Once Here For Each P In Array(2,3,5,7,11,13,17,19,23,29,31,37,41,43,47) n(P) = 1& Next P I'm not sure of your second question, as I se eyou already have a solution to your Odd/Even query. -- Dana DeLouis "Paul Black" wrote in message oups.com... 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. The results should be as follows :- 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 ' 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 |
Count Specific Total Numbers from a Set
Thanks for the reply Dana,
I will have a play around over the next week with your suggestions. What does the "&" mean in "n(P) = 1&" please. Also, it would be appreciated if you could just explain what the variables "R", "n", "NPrime", "PrimeQ" & "S" actually do please. It appears that the variable "R" means one thing at the beginning of the code and something else further down. Thanks in Advance. All the Best. Paul On Nov 4, 12:42 am, "Dana DeLouis" wrote: Instead of Select Case, and looping 49 times, I suppose we could loop 15 times. As you can see, there are many different ideas one can use. The idea here is that the Array is initialized with zero anyway, and we are just setting a few of them to 1. Dim P As Variant ' Do Once Here For Each P In Array(2,3,5,7,11,13,17,19,23,29,31,37,41,43,47) n(P) = 1& Next P I'm not sure of your second question, as I se eyou already have a solution to your Odd/Even query. -- Dana DeLouis "Paul Black" wrote in message oups.com... 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. The results should be as follows :- 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 ' 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- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 01:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com