Can this Code be Condensed
Paul: Not sure what you are doing this time. If you have 6 numbers (A to F)
and you are looking at the 1st digits then the maximum sum of the 1st digits
is 9 + 8 + 7 + 6 + 5 + 4 = 39. Your code only has 9 instead of 39
I changed
from:
ActiveCell.Offset(0, 0).Value = "First Digits ="
to:
ActiveCell.Offset(0, 0).Value = "Sum First Digits ="
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(39) As Double
Dim sum As Long
Application.ScreenUpdating = False
Range("B4").Select
Dim results(49) As Long
nMinA = 1
nMaxF = 49
For i = 1 To 39
nType(i) = 0
Next i
For i = nMinA To 9
results(i) = i
Next i
For i = 10 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 39
ActiveCell.Offset(0, 0).Value = "Sum 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 39
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
"Paul Black" wrote:
Thanks Joel,
The code now is ...
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
Range("B4").Select
Dim results(49) As Long
nMinA = 1
nMaxF = 49
For i = nMinA To 9
nType(i) = 0
results(i) = i
Next i
For i = 10 To nMaxF
nType(i) = 0
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
.... but I keep getting an error on line ...
nType(i) = 0
.... and if I edit it out I get an error on line ...
nType(sum) = nType(sum) + 1
.... for some reason.
Thanks in Advance.
All the Best.
Paul
On Nov 17, 11:39 am, Joel wrote:
nMinA = 1
nMaxF = 49
For i = nMinA to 9
nType(i) = 0
results(i) = i
Next i
For i = 10 To nMaxF
nType(i) = 0
results(i) = Int(i \ 10)
Next i
"PaulBlack" wrote:
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
|