Add digits together

[S.H.A.D.O.]

Good afternoon,

The code below adds together ALL the individual digits of each combination and lists those totals, it also lists the total combinations associated with each of those totals.

The Excel formula if the combination was in cells A1:F1 would be...

=SUMPRODUCT(INT(A1:F1/10)+MOD(A1:F1,10))

My question is, how can I adapt this code to add those totals together and list the total combinations associated with each of those totals.

For example, if the combination was 05, 44, 46, 48, 50, 52, then 5+4+4+4+6+4+8+5+5+2 = 47, and 47 would be 4+7 = 11.

I know that the minimum would be 2 and the maximum would be 15.

The Excel formula if the combination total was in cell A1 would be...

=INT(A1/10+MOD(A1,10))

I just can't seem to incorporate this into the existing code and get it to work.

Here is the code...

Option Explicit
Option Base 1
Const MinA As Integer = 1
Const MaxF As Integer = 59
    
Sub Root_Sum()
    Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
    Dim Num As Long
    Dim nums(59) As Long
    Dim Root As Long
    Dim Map(11 To 76) As Double
    Dim n As Long
    Dim Total As Long
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
    Columns("A:B").ClearContents
    For Num = LBound(nums) To UBound(nums)
        nums(Num) = Num \ 10 + Num Mod 10
    Next Num
    For A = MinA To MaxF - 5
        For B = A + 1 To MaxF - 4
            For C = B + 1 To MaxF - 3
                For D = C + 1 To MaxF - 2
                    For E = D + 1 To MaxF - 1
                        For F = E + 1 To MaxF
                            Root = nums(A) + nums(B) + nums(C) + _
                                   nums(D) + nums(E) + nums(F)
                            Map(Root) = Map(Root) + 1
                        Next F
                    Next E
                Next D
            Next C
        Next B
    Next A
    For n = LBound(Map) To UBound(Map)
        Total = Total + Map(n)
        ActiveCell.Offset(n - LBound(Map), 0).Value = n
        ActiveCell.Offset(n - LBound(Map), 1).Value = Map(n)
    Next n
    ActiveCell.Offset(n - LBound(Map), 1).Value = Total
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

Thanks very much in advance.

 

[JackDanIce]

Would you mind posting your final code? Where do you use

=INT(existing total / 10 + MOD (existing total, 10))

 

[S.H.A.D.O.]

Would you mind posting your final code? Where do you use

=INT(existing total / 10 + MOD (existing total, 10))

Hi Jack,

That was just an example to try and relay what I needed to do, I admit that I could have described that better, sorry for the confusion.

What I was trying to say was that I needed to take the existing totals of the initial digits being added together, like they do in the original code posted, and then add them together again, like it does in the piece of code...

    For Num = LBound(Digits) To UBound(Digits)
        Digits(Num) = Num \ 10 + Num Mod 10
    Next Num

Here is the amended code that works...

Option Explicit
Option Base 1

Const MinA As Integer = 1
Const MaxF As Integer = 59
    
Sub Root_Sum()
    Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
    Dim Num As Long
    Dim nums(59) As Long
    Dim Root As Long
    Dim Map(2 To 15) As Double ' [B]<--- Changed to accomodate the minimum & maximum values.[/B]
    Dim n As Long
    Dim Total As Long
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
    Columns("A:B").ClearContents
    For Num = LBound(nums) To UBound(nums)
        nums(Num) = Num \ 10 + Num Mod 10
    Next Num
    For A = MinA To MaxF - 5
        For B = A + 1 To MaxF - 4
            For C = B + 1 To MaxF - 3
                For D = C + 1 To MaxF - 2
                    For E = D + 1 To MaxF - 1
                        For F = E + 1 To MaxF
                            Root = nums(A) + nums(B) + nums(C) + _
                                   nums(D) + nums(E) + nums(F)
                            Map(Root \ 10 + Root Mod 10) = _
                                Map(Root \ 10 + Root Mod 10) + 1 ' [B]<--- Changed to further add digits together again.[/B]
                        Next F
                    Next E
                Next D
            Next C
        Next B
    Next A
    For n = LBound(Map) To UBound(Map)
        Total = Total + Map(n)
        ActiveCell.Offset(n - LBound(Map), 0).Value = n
        ActiveCell.Offset(n - LBound(Map), 1).Value = Map(n)
    Next n
    ActiveCell.Offset(n - LBound(Map), 1).Value = Total
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

Thanks, I appreciate your time on this.