Sum up all digits in a given number

[kelly mort]

Hello,
I need help with this algorithm. I have a cell say A2 that contains a given number and I want to use these rules for it:
(1). Sum all the characters up
(2). If the result form the sum is more than a 1-digit number, then we sum the characters of our result up again till we get a single digit answer.
The numbers 11 and 22 are excluded. That is, when our sums yield 11 or 22, we should skip the further sums.
(3). When the result has reached 11 or 22 or a single digit from the other non-single digit numbers, then display that on a message box or the immediate window.
These are sample examples of output I am looking for:

1.    181991 = 1+8+1+9+9+1 = 29 = 2+9 = 11
2.    281991 = 2+8+1+9+9+1 = 30 = 3+0 = 3
3.    887988 = 8+8+7+9+8+8 = 48 = 4+8 = 12 = 1+2 = 3

So for 181991 result is 11
So for 281991 result is 3
So for 887988 result is 3

 

[Joe4]

Here is a User Defined Function you can use for this:

Function NumberSum(myNum As Variant) As Variant

    Dim n As Long
    Dim i As Long
    Dim l As Long
    Dim s As Long

    Application.Volatile

'   Check to make sure entry is numeric
    If Not IsNumeric(myNum) Then
        NumberSum = "Not a valid number"
        Exit Function
    End If
    
'   Set initial value of number to loop through
    n = myNum
    
'   Loop through all characters
    Do
'       Get length of number
        l = Len(CStr(n))
'       Loop through all digits and get sum
        For i = 1 To l
            s = s + Mid(n, i, 1)
        Next i
'       Check value of sum
        If (s = 11) Or (s = 22) Or (s < 10) Then
'           Return value if it meets any of conditions above and exit loop
            NumberSum = s
            Exit Do
        Else
'       Otherwise reset values and keep going
            n = s
            s = 0
        End If
    Loop
    
End Function

So, you would use this like any other function, either directly on the spreadsheet, or in VBA.

On the worksheet, if you had your number in cell A1, the formula would look like:
=NumberSum(A1)

In VBA, you could call it like any other function, i.e.
MsgBox NumberSum(181991)

 

[WaterGypsy]

Beaten to it by a better answer

You could try something like this:

Sub Macro1()
'
Dim iIn As String
Dim iC As Integer
Dim iTot As Integer
Dim bStop As Boolean
iIn = Range("a2")

bStop = False
Do Until bStop = True
    iTot = 0
    For iC = 1 To Len(iIn)
        iTot = iTot + Mid(iIn, iC, 1)
    Next
    If iTot < 10 Or iTot = 11 Or iTot = 2 Then
        bStop = True
    Else
        iIn = iTot
    End If
Loop
MsgBox iTot
    


 
End Sub

 

[kelly mort]

Beaten to it by a better answer

You could try something like this:

Sub Macro1()
'
Dim iIn As String
Dim iC As Integer
Dim iTot As Integer
Dim bStop As Boolean
iIn = Range("a2")

bStop = False
Do Until bStop = True
    iTot = 0
    For iC = 1 To Len(iIn)
        iTot = iTot + Mid(iIn, iC, 1)
    Next
    If iTot < 10 Or iTot = 11 Or iTot = 2 Then
        bStop = True
    Else
        iIn = iTot
    End If
Loop
MsgBox iTot
    


 
End Sub

Great!!!!

Exactly what I am looking for.

Thanks

 

[kelly mort]

Here is a User Defined Function you can use for this:

Function NumberSum(myNum As Variant) As Variant

    Dim n As Long
    Dim i As Long
    Dim l As Long
    Dim s As Long

    Application.Volatile

'   Check to make sure entry is numeric
    If Not IsNumeric(myNum) Then
        NumberSum = "Not a valid number"
        Exit Function
    End If
    
'   Set initial value of number to loop through
    n = myNum
    
'   Loop through all characters
    Do
'       Get length of number
        l = Len(CStr(n))
'       Loop through all digits and get sum
        For i = 1 To l
            s = s + Mid(n, i, 1)
        Next i
'       Check value of sum
        If (s = 11) Or (s = 22) Or (s < 10) Then
'           Return value if it meets any of conditions above and exit loop
            NumberSum = s
            Exit Do
        Else
'       Otherwise reset values and keep going
            n = s
            s = 0
        End If
    Loop
    
End Function

So, you would use this like any other function, either directly on the spreadsheet, or in VBA.

On the worksheet, if you had your number in cell A1, the formula would look like:
=NumberSum(A1)

In VBA, you could call it like any other function, i.e.
MsgBox NumberSum(181991)

Great one here too.

I am very grateful.