Can this Code be Condensed
Brilliant Joel, thanks very much.
My final program on this is to calculate the first digits.
I know using Int(i \ 10) gives the correct results for numbers 10 to
49 ( 1 to 4 ) but NOT for numbers 1 to 9, it gives zeros. I have
adapted the code to try and achieve this but it will not work.
Here is the code :-
Option Explicit
Option Base 1
Sub First_Digits()
Dim Start As Double
Start = Timer
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim i As Integer
Dim nMinA As Integer
Dim nMaxF As Integer
Dim nType(9) As Double
Dim sum As Long
Application.ScreenUpdating = False
'Sheets("Results").Select
Range("B4").Select
Dim results(49) As Long
nMinA = 1
nMaxF = 49
For i = 1 To 9
nType(i) = 0
Next i
For i = nMinA To nMaxF
results(i) = Int(i \ 10)
Next i
For A = nMinA To nMaxF - 5
For B = A + 1 To nMaxF - 4
For C = B + 1 To nMaxF - 3
For D = C + 1 To nMaxF - 2
For E = D + 1 To nMaxF - 1
For F = E + 1 To nMaxF
sum = results(A) + results(B) + results(C) + results(D) + results(E)
+ results(F)
nType(sum) = nType(sum) + 1
Next F
Next E
Next D
Next C
Next B
Next A
For i = 1 To 9
ActiveCell.Offset(0, 0).Value = "First Digits ="
ActiveCell.Offset(0, 1).Value = i
ActiveCell.Offset(0, 2).Value = nType(i)
ActiveCell.Offset(1, 0).Select
Next i
ActiveCell.Offset(0, 0).Value = "Total Combinations Produced"
sum = 0
For i = 1 To 9
sum = sum + nType(i)
Next i
ActiveCell.Offset(0, 2).Value = sum
ActiveCell.Offset(2, 0) = "This Program Took " & _
Format(((Timer - Start) / 24 / 60 / 60), "hh:mm:ss") & " To
Process "
Range("B68").Select
Application.ScreenUpdating = True
End Sub
Thanks in Advance.
All the Best.
Paul
On Nov 12, 8:11 pm, Joel wrote:
Paul: I think this is what Dana was suggesting. It runs 2x faster than my
other code.
Option Explicit
Option Base 1
Sub Sum_Of_Digits()
Dim Start As Double
Start = Timer
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim i As Integer
Dim nMinA As Integer
Dim nMaxF As Integer
Dim nType(70) As Double
Dim sum As Long
Application.ScreenUpdating = False
Sheets("Results").Select
Range("B4").Select
Dim results(49) As Long
nMinA = 1
nMaxF = 49
For i = 11 To 70
nType(i) = 0
Next i
For i = nMinA To nMaxF
results(i) = i \ 10 + i Mod 10
Next i
For A = nMinA To nMaxF - 5
For B = A + 1 To nMaxF - 4
For C = B + 1 To nMaxF - 3
For D = C + 1 To nMaxF - 2
For E = D + 1 To nMaxF - 1
For F = E + 1 To nMaxF
sum = results(A) + results(B) + results(C) + results(D) + results(E) +
results(F)
nType(sum) = nType(sum) + 1
Next F
Next E
Next D
Next C
Next B
Next A
For i = 11 To 70
ActiveCell.Offset(0, 0).Value = "Sum Of Digits ="
ActiveCell.Offset(0, 1).Value = i
ActiveCell.Offset(0, 2).Value = nType(i)
ActiveCell.Offset(1, 0).Select
Next i
ActiveCell.Offset(0, 0).Value = "Total Combinations Produced"
sum = 0
For i = 1 To 70
sum = sum + nType(i)
Next i
ActiveCell.Offset(0, 2).Value = sum
ActiveCell.Offset(2, 0) = "This Program Took " & _
Format(((Timer - Start) / 24 / 60 / 60), "hh:mm:ss") & " To Process "
Range("B68").Select
Application.ScreenUpdating = True
End Sub
"PaulBlack" wrote:
Hi Joel & Dana, thanks for the replies.
Joel.
Your second code does indeed run faster than the first and produces
the correct results thank you.
Dana,
I have done as you suggested but can't get the code to work. This is
what I have :-
Option Explicit
Option Base 1
Sub Sum_Of__Digits()
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 nMinA As Integer
Dim nMaxF As Integer
Dim S As String
Dim StartTime As Double
Dim Total As Long
Dim n(1 To 49) As Long
Dim SumOfDigits(11 To 70) As Long
StartTime = Timer
nMinA = 1
nMaxF = 49
For R = 1 To 49
n(R) = A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D
\ 10 _
+ D Mod 10 + E \ 10 + E Mod 10 + F \ 10 + F Mod 10
Next R
For A = nMinA To nMaxF - 5
For B = A + 1 To nMaxF - 4
For C = B + 1 To nMaxF - 3
For D = C + 1 To nMaxF - 2
For E = D + 1 To nMaxF - 1
For F = E + 1 To nMaxF
R = n(A) + n(B) + n(C) + n(D) + n(E) + n(F)
SumOfDigits(R) = SumOfDigits(R) + 1
Next F
Next E
Next D
Next C
Next B
Next A
For R = 11 To 70
Cells(R + 1, 1) = R
Cells(R + 1, 2) = Format(SumOfDigits(R), "#,0")
Total = Total + SumOfDigits(R)
Next R
S = Format(Total, "#,0")
Cells(R + 1, 2) = S
Debug.Print Timer - StartTime
If Total = WorksheetFunction.Combin(49, 6) Then
MsgBox "Total is correct: " & S
Else
MsgBox "Error in Total: " & S
End If
End Sub
Thanks in Advance.
All the Best.
Paul
On Nov 12, 2:20 pm, Joel wrote:
Dana: Isn't what you are suggesting the same thing I posted in my code?
sum = A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10 _
+ D Mod 10 + E \ 10 + E Mod 10 + F \ 10 + F Mod 10
nType(sum) = nType(sum) + 1
"Dana DeLouis" wrote:
Another option would be similar to previous code.
The code is figuring out the sum of digits over and over.
Try doing it once per each of the 49 digits.
Dim n(1 To 49) As Long
' Do Once Here
For R = 1 To 49
n(R) = 'Sum of digits
Next R
Now...just add. :)
a+b+c...ect
--
Dana DeLouis
"PaulBlack" wrote in message
oups.com...
Hi everyone,
Is there any way that the code below can be condensed or shortened
please
It cycles through six number combinations and counts the total of ALL
the digits in each combination. So, combination 10,11,12,13,14,15 will
= 1+0+1+1+1+2+1+3+1+4+1+5 = 21.
The total of ALL the digits range from 11 to 70.
Her is the code :-
Option Explicit
Option Base 1
Sub Sum_Of_Digits()
Dim Start As Double
Start = Timer
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim i As Integer
Dim nMinA As Integer
Dim nMaxF As Integer
Dim nType(70) As Double
Application.ScreenUpdating = False
Sheets("Results").Select
Range("B4").Select
nMinA = 1
nMaxF = 49
For i = 11 To 70
nType(i) = 0
Next i
For A = nMinA To nMaxF - 5
For B = A + 1 To nMaxF - 4
For C = B + 1 To nMaxF - 3
For D = C + 1 To nMaxF - 2
For E = D + 1 To nMaxF - 1
For F = E + 1 To nMaxF
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 1 Then nType(1) = nType(1)
+ 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 2 Then nType(2) = nType(2)
+ 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 3 Then nType(3) = nType(3)
+ 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 4 Then nType(4) = nType(4)
+ 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 5 Then nType(5) = nType(5)
+ 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 6 Then nType(6) = nType(6)
+ 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 7 Then nType(7) = nType(7)
+ 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 8 Then nType(8) = nType(8)
+ 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 9 Then nType(9) = nType(9)
+ 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 10 Then nType(10) =
nType(10) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 11 Then nType(11) =
nType(11) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 12 Then nType(12) =
nType(12) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 13 Then nType(13) =
nType(13) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 14 Then nType(14) =
nType(14) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 15 Then nType(15) =
nType(15) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 16 Then nType(16) =
nType(16) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 17 Then nType(17) =
nType(17) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 18 Then nType(18) =
nType(18) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 19 Then nType(19) =
nType(19) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 20 Then nType(20) =
nType(20) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 21 Then nType(21) =
nType(21) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 22 Then nType(22) =
nType(22) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 23 Then nType(23) =
nType(23) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 24 Then nType(24) =
nType(24) + 1
If A \ 10 + A Mod 10 + B \ 10 + B Mod 10 + C \ 10 + C Mod 10 + D \ 10
+ D Mod 10 _
+ E \ 10 + E Mod 10 + F \ 10 + F Mod 10 = 25 Then nType(25) =
nType(25) + 1
If A \ 10 + A Mod 10 + B \- Hide quoted text -
- Show quoted text -...
read more
|