Using VBA to add digits togeather

[mchad]

Hello,

I am trying to create a function that will let me input a number, and the function will do the following:

1. Add the digits in that number togeather
2. Check if the resulting sum is a single digit (1-9), 11, or 22;
3. Return the number if the #2 is true
4. Start Back at #1 with the new number if #2 is false .

The number of digits will fluxuate. I have been trying to use an array nut I have been having trouble splitting the number into the array effectively.

 

[mchad]

I realized I may not have been as clear as I should have been.

I want to be able to put in a number like '12345'
Have the function add 1+2+3+4+5=15
Check to see if that 15 is 1-9, 11,22
If it is not then repeat the process with the new numeber (1+5=6) until the number is 1-9, 11, or 22

 

[welshgasman]

I have no clue as to what your repeat process is.

However look at string functions Len() and Mid() to put items into array and then sum them, or just add them to a variable.

 

[FormR]

Hi, welcome to the forum!

Here's a UDF you could try:

Function fx(n As Long)
Dim i As Long, j As Long
For i = 1 To Len(n)
    j = j + Val(Mid(CStr(n), i, 1))
Next i
If j <= 9 Or j = 11 Or j = 22 Then
    fx = j
Else
    fx = fx(j)
End If
End Function

 

[mchad]

I have no clue as to what your repeat process is.

However look at string functions Len() and Mid() to put items into array and then sum them, or just add them to a variable.

Thank you so much, that Mid() suggestion was exactly what I need, oddly enough I had already figured out the repeat unil part, it was the seperating and adding togeather that was giving me trouble.

In case you are curious, this is what I ended up using.

Function ElementReduction(Element As Integer) As Integer
    Dim i As Integer
    Dim Leng As Integer
    Dim TEle As Integer
    Dim EleLen As String
  
  
    Do Until Element < 10 Or Element = 11 Or Element = 22
        i = 1
        TEle = 0
        EleLen = Str(Element)
        Leng = Len(LTrim(EleLen))
      
        While i <= Leng
            TEle = TEle + Mid(Int(Element), i, 1)
            i = i + 1
        Wend
      
        Element = TEle
  
    Loop
ElementReduction = Element

End Function

 

[mchad]

Hi, welcome to the forum!

Here's a UDF you could try:

Function fx(n As Long)
Dim i As Long, j As Long
For i = 1 To Len(n)
    j = j + Val(Mid(CStr(n), i, 1))
Next i
If j <= 9 Or j = 11 Or j = 22 Then
    fx = j
Else
    fx = fx(j)
End If
End Function

Thank you for responding, this is very simialr to what I ended up with which I put in one of the earlier replies.

 

[FormR]

Glad to hear you got it figured out

 

[Peter_SSs]

Some comments.

1. When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.
   
   
2. You gave an example of a 5-digit number (12345) as a starting value. With some other 5-digit numbers (& any longer numbers) your post #5 UDF fails - see row 2 (& row 8) below. It fails because you have declared the input 'Element' as Integer. An Integer can only be as big 32,767 so may not be the best data type. In any case my understanding is that vba actually converts all Integers to Long data type before working with them so I never use Integer and always use Long for whole numbers. So you could change those declarations in your UDF.
   
   
3. Your UDF does not actually do the steps you listed in post #1. See cells B6:B7 below where it can be seen that the UDF skips step 1 (at least in the first pass). I assume however that those are the desired results.
   
   
4. You will likely stick with a UDF but this can also be done with a recursive LAMBDA function - see column C below. To implement
   • In the Name Manager (Formulas ribbon tab)
   • New...
   • In the 'Name' box enter ADDDOWN
   • In the 'Refers to' box paste this formula =LAMBDA(n,IF(OR(n={1,2,3,4,5,6,7,8,9,11,22}),n,ADDDOWN(SUM(--MID(n,SEQUENCE(LEN(n)),1)))))
   • OK -> Close
   • Use the function as shown in this Mini Sheet

mchad.xlsm
	A	B	C
1		Post #5 UDF	LAMBDA function
2	12345	6	6
3	33333	#NUM!	6
4	994	22	22
5	10	1	1
6	11	11	11
7	22	22	22
8	1234567890	#NUM!	9
Add Digits

Cell Formulas
Range	Formula
B2:B8	B2	=ElementReduction(A2)
C2:C8	C2	=ADDDOWN(A2)

 

[FormR]

this can also be done with a recursive LAMBDA function

Hi Peter, I think this was an Access question (it's in the Access forum at least).

 

[Peter_SSs]

Hi Peter, I think this was an Access question (it's in the Access forum at least).

Thanks for putting me straight. I got here via a link and didn't even think about what forum the link might have taken me to.

So my response can be ignored (sorry @mchad) - but I did find it interesting to dabble again with recursive lambda so I'm still happy.