Re: Count Specific Total Numbers from a Set
- From: Joel <Joel@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 1 Nov 2007 06:44:03 -0700
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 <J...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Here is a different version of the code
Option Explicit
Option Base 0
Sub Prime()
Dim nVal As Variant
Dim n As Integer
Dim numberPrimes As Integer
Dim number(6) As Variant
Dim A, B, C, D, E, F As Integer
nVal = Array(0, 0, 0, 0, 0, 0, 0)
For A = 1 To 44
number(0) = A
For B = number(0) + 1 To 45
number(1) = B
For C = number(1) + 1 To 46
number(2) = C
For D = number(2) + 1 To 47
number(3) = D
For E = number(3) + 1 To 48
number(4) = E
For F = number(4) + 1 To 49
number(5) = F
numberPrimes = nValF(number())
nVal(numberPrimes) = nVal(numberPrimes) + 1
Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select
For n = 1 To 7
Range("A" & n).Value = n - 1
Range("B" & n).Value = Format(nVal(n - 1), "#,0")
Next n
End Sub
Private Function nValF(ByRef number() As Variant) As Integer
Dim n As Integer
nValF = 0
For n = 0 To 5
Select Case number(n)
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nValF = nValF + 1
End Select
Next n
End Function
"Joel" wrote:
I get the same answers as you
Option Explicit
Option Base 0
Dim A As Integer, B As Integer, C As Integer, D As Integer
Dim E As Integer, F As Integer
Sub Prime()
Dim nVal As Variant
Dim n As Integer
Dim numberPrimes As Integer
nVal = Array(0, 0, 0, 0, 0, 0, 0)
For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
numberPrimes = nValF()
nVal(numberPrimes) = nVal(numberPrimes) + 1
Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select
For n = 1 To 7
Range("A" & n).Value = n - 1
Range("B" & n).Value = Format(nVal(n - 1), "#,0")
Next n
End Sub
Private Function nValF() As Integer
Dim nVal As Integer
nVal = 0
Select Case A
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nValF = nValF + 1
End Select
Select Case B
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nValF = nValF + 1
End Select
Select Case C
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nValF = nValF + 1
End Select
Select Case D
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nValF = nValF + 1
End Select
Select Case E
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nValF = nValF + 1
End Select
Select Case F
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nValF = nValF + 1
End Select
End Function
"Paul Black" wrote:
Sorry to be a pain Joel,
I really want the "A" to start at 1 To 44.
Also, the program does NOT produce the total combinations with NO
Prime Numbers.
I calculated the totals using formulas and I got :-
0 = 1,344,904
1 = 4,173,840
2 = 4,869,480
3 = 2,722,720
4 = 765,765
5 = 102,102
6 = 5,005
Total combinations = 13,983,816
Thanks in Advance.
All the Best.
Paul
On Oct 31, 12:11 pm, Joel <J...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
You then need to change A to start at 0. Option base is independant from
data starting at 0. The option base only concenrs itself with the first
index of an arrray either being 0 or 1. The data items are in the array DATA
and not an index.
Option Explicit
Option Base 1
Dim A As Integer, B As Integer, C As Integer, D As Integer
Dim E As Integer, F As Integer
Sub Prime()
Dim nVal As Variant
Dim Data As Variant
Dim n As Integer
Data = Array(0, 1, 2, 3, 4, 5)
nVal = Array(0, 0, 0, 0, 0, 0)
For A = 0 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
For n = 1 To UBound(Data)
If A = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If B = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If C = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If D = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If E = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
If F = Data(n) Then
nVal(n) = nVal(n) + 1
Exit For
End If
Next n
Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select
For n = 1 To 6
Range("A" & n).Value = n
Range("B" & n).Value = Format(nVal(n), "#,0")
Next n
End Sub
Private Function nVal(inVal As Integer) As Integer
Select Case A
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case B
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case C
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case D
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case E
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
Select Case F
Case 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
nVal = inVal + 1
End Select
"Paul Black" wrote:
Thanks for the reply Joel,
Actually, I want combinations that contain 0,1,2,3,4,5 and 6 Prime
Numbers, so I don't need the Option Base 1 in this instance.
I tried adapting your code to accomodate this but to no avail.
Thanks in Advance.
All the Best.
Paul
On Oct 31, 10:49 am, Joel <J...@xxxxxxxxxxxxxxxxxxxxxxxxx> 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
- Follow-Ups:
- Re: Count Specific Total Numbers from a Set
- From: Paul Black
- Re: Count Specific Total Numbers from a Set
- References:
- Re: Count Specific Total Numbers from a Set
- From: Paul Black
- Re: Count Specific Total Numbers from a Set
- Prev by Date: Re: A negative to using Application.Onkey
- Next by Date: Re: Resubmit of Macro needed to search 2 columns
- Previous by thread: Re: Count Specific Total Numbers from a Set
- Next by thread: Re: Count Specific Total Numbers from a Set
- Index(es):
Relevant Pages
|
