Sum up all characters in a given string

[kelly mort]

Hello,
I have this table and I am looking for a cooler way to use it as a guide and create an algorithm from it.
So from the table, 1 is assigned to A, J and S. So say a word like “KELLY”, I want to add up all the characters by using their number value. That’s “Kelly” = 2+5+3+3+7 = 20.


Then I use these further rules on the outcome:
(1). 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.
(2). 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.    “Kelly” = 2+5+3+3+7 = 20 = 2+0 = 2
2.    “mort” = 4+6+9+2 = 21 = 2+1 = 3
3.    “hello” = 8+5+3+3+6 = 25 = 2+5 = 7

So for “Kelly” result is 2
So for “mort” result is 3
So for “hello” result is 7

Num    LT    LT    LT
1    A    J    S
2    B    K    T
3    C    L    U
4    D    M    V
5    E    N    W
6    F    O    X
7    G    P    Y
8    H    Q    Z
9    I    R

 

[kelly mort]

Marcelo, we'll need Kelly to validate this, but biiiii = 2 + 9 + 9 + 9 + 9 + 9 = 47. This is not 11 or 22, so you take it mod 9 to get 2. However, 4 + 7 = 11, so I think it should stop there. Also, consider a word such as ii. 9 + 9 = 18. You take this mod 9 to get 0. Shouldn't it be 9?

Kelly, please address the points above. Also, I'm a little unsure what you want. We have created the UDFs to give you the result. Are you talking about assigning it to a VBA variable? You can call either of these functions from within other VBA code. Or how do you want the Msgbox? For example, do you want it so that when someone enters a word in a given cell or range, a msgbox will show up with the result? We can make a change event to do that.

Marcelo, we'll need Kelly to validate this, but biiiii = 2 + 9 + 9 + 9 + 9 + 9 = 47. This is not 11 or 22, so you take it mod 9 to get 2. However, 4 + 7 = 11, so I think it should stop there. Also, consider a word such as ii. 9 + 9 = 18. You take this mod 9 to get 0. Shouldn't it be 9?

Kelly, please address the points above. Also, I'm a little unsure what you want. We have created the UDFs to give you the result. Are you talking about assigning it to a VBA variable? You can call either of these functions from within other VBA code. Or how do you want the Msgbox? For example, do you want it so that when someone enters a word in a given cell or range, a msgbox will show up with the result? We can make a change event to do that.

Your claim is valid.

And for what I want, I will use those results to do something else later on.

So I want to assign it to a variable since that's what I clearly understand so far.

When I run this code, I want a message box to tell me the output.

In that way I will be at a point of understanding the script a bit so as to modify it later on my own.

Thanks

 

[Eric W]

I'm still a little unclear about how you want to initiate the macro. It seems you want to use this code in a bigger project later. But you can call either of the UDFs like so:

Sub temp1()

    MyVariable = MySum("Kelly", Range("A2:B27"))
    
    MsgBox "Result is: " & MyVariable
    
End Sub

Here's a commented version of my UDF:

Function MySum(ByVal MyWord As String, ByVal MyTable As Range)
Dim MyTab As Variant, i As Long, w As Long, s As String

    MyTab = MyTable.Value       ' Save the Table values in an interal array for performance
    MySum = 0                   ' Set the initial total to 0
    For i = 1 To Len(MyWord)    ' Check each letter in the word
' Use UCase to make sure it's upper case, then Mid to get the individual letter, then
' Asc to get the ASCII code, then subtract 64 ("A" = 65) to get the offset into the table.
        w = Asc(Mid(UCase(MyWord), i, 1)) - 64
        MySum = MySum + MyTab(w, 2)     ' add the next value
    Next i
    
' We have the initial sum, so now iterate until we find a stopping condition
    While MySum > 9 And MySum <> 11 And MySum <> 22
        s = MySum       ' we need to process more? Save the value as a string
        MySum = 0       ' reset the total
        For i = 1 To Len(s)                 ' check each value in the string
            MySum = MySum + Mid(s, i, 1)    ' add the value
        Next i
    Wend
    
End Function

 

[kelly mort]

Okay I think i am on the way to understanding.

One last thing :

Instead of using that table, is there a way to have that table (that's those letters) embedded in the code?

I just want to avoid the code referring to the worksheet.


With the initiation of the code, that will be through a command button.

 

[Eric W]

Lots of ways, here's one:

Function MySum(ByVal MyWord As String)
Dim MyTab As Variant, i As Long, w As Long, s As String

' Values for each letter in ABC order
    MyTab = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8)
    MySum = 0                   ' Set the initial total to 0
    For i = 1 To Len(MyWord)    ' Check each letter in the word
' Use UCase to make sure it's upper case, then Mid to get the individual letter, then
' Asc to get the ASCII code, then subtract 65 ("A" = 65) to get the offset into the table.
        w = Asc(Mid(UCase(MyWord), i, 1)) - 65
        MySum = MySum + MyTab(w)     ' add the next value
    Next i
    
' We have the initial sum, so now iterate until we find a stopping condition
    While MySum > 9 And MySum <> 11 And MySum <> 22
        s = MySum       ' we need to process more? Save the value as a string
        MySum = 0       ' reset the total
        For i = 1 To Len(s)                 ' check each value in the string
            MySum = MySum + Mid(s, i, 1)    ' add the value
        Next i
    Wend
    
End Function

Sub temp1()

    MyVariable = MySum("Kelly")
    
    MsgBox "Result is: " & MyVariable
    
End Sub

 

[kelly mort]

Sure I like this.

Nice to have you here.

Have a nice time

 

[Eric W]

Glad we could help.

 

[Rick Rothstein]

Here is another function that you can consider (it assumes your letter/number relationship will never change since I use a mathematical calculation to determine it). Also note that I changed the name of the function to something I consider more descriptive.

[table="width: 500"]
[tr]
	[td]Function LetterSum(ByVal s As String) As Long
  If Not s Like "*[!A-Za-z ]*" Then
    LetterSum = Evaluate("SUM(IF({1},MOD(CODE(MID(""" & UCase(Replace(s, " ", "")) & """,ROW(1:" & Len(Replace(s, " ", "")) & "),1))-65,9)+1))")
    Do While LetterSum > 9 And LetterSum <> 11 And LetterSum <> 22
      LetterSum = Evaluate(Replace(StrConv(LetterSum, vbUnicode) & 0, Chr(0), "+"))
    Loop
  End If
End Function[/td]
[/tr]
[/table]

Here is a macro showing it being used...

Sub Test()
  Dim MyVariable
  MyVariable = LetterSum(InputBox("Input text (letters and/or spaces) to be evaluated"))
  MsgBox "Result is: " & MyVariable
End Sub

Note: My function returns 0 if any invalid characters are part of the text being evaluated. Valid characters are A thru Z, a thru z (lower case letters are made upper case before being evaluated) and the space character (which is ignored during the calculation, but tolerated in case a name is made up of two names such as "Mary Ann".

 

[kelly mort]

@Rick Rothstein,

Your code is very cool. You always have a shorter version of scripts.

I appreciate it