# Excel Experts

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### CouchMaster

The answer will always be whole digits
Is there a way to add the single digits in the answer together (1+2+3) without having to re-enter them separately? Perhaps a command that says 'add the single digits in that cell'?

#### OBP

With VBA you can do it, you may be able to do it with Excel functions.
Basically you convert the answer to a string, split the string in to single parts and then convert them back to numbers and add them together.

#### OBP

Assuming your answer is Cell b2 this code will split up the answer in to it's components, assign them to an array and then add them together and put the answer in to Cell C2.

Private Sub CommandButton1_Click()
Dim values(1 To 10) As String, answer As String, count As Integer, newanswer As Integer

Range("b2").Select

For count = 2 To Len(answer)
values(count - 1) = Int(Mid(answer, count, 1))
MsgBox values(count - 1)
Next count
For count = 1 To Len(answer) - 1
Next count

End Sub

The message boxs are ust ther to show you what it is doing

#### CouchMaster

Thanks OBP, I'll give it a shot!

#### Zack Barresse

This would be much easier as a function ...

Code:
``````Option Explicit

Public Function SumCell(celRef As Range) As Long
Dim i As Long
For i = 1 To Len(celRef)
SumCell = CLng(SumCell) + CLng(Mid(celRef, i, 1))
Next i
End Function``````
Call from the worksheet as such ...

=SumCell(A1)

Where A1 holds your 123 value. This procedure must go in a Standard Module. This will NOT calculate everytime it's dependent cell(s) are calculated. For multiple cells ...

Code:
``````Public Function SumCell(celRef As Range) As Long
Dim c As Range
Dim i As Long
If celRef.Cells.Count = 1 Then
For i = 1 To Len(celRef)
SumCell = CLng(SumCell) + CLng(Mid(celRef, i, 1))
Next i
Else
For Each c In celRef
For i = 1 To Len(c)
SumCell = CLng(SumCell) + CLng(Mid(c, i, 1))
Next i
Next c
End If
End Function``````

HTH

#### OBP

Zack, nice piece of code.

#### Zack Barresse

Thanks OBP.

I believe there is a way to do this strictly with Excel worksheet functions, but I've fogotten how to do so! I'm doing a little recon work now trying to dig it up.

Btw, hope ya'll had a great Christmas.

#### OBP

Yes thanks and you?
I was on here for a while just in case there were any really desperate posters.

#### Zack Barresse

I had a wonderful Christmas. The kids had a great time and it made my weekend great.

Tell the truth, I barely got on this weekend to check my email! Playing with my presents mostly (my wife bought me a guitar).

#### blaqDeaph

You Don't need functions to do this.

You just need to use MID() to get each number, and sum them together. The limitation to this is that there is a limited number of digits that can be entered.

To add 123 in cell A1:

MID(A1, 1, 1)+MID(A1, 2, 1)+MID(A1, 3, 1)

etc.

To make it a variable number, test MID() for > 0, and use a list of numbers as the start refrence.

#### OBP

blaqDeaph, the problem is you do not know how many characters to cater for with that method i.e. 123 or 1234567. The Mid()>0 test would have to be on every possible place of mid otherwise you get a #value error, not so bad if you put each test in a seperate column and then sum the columns.

#### Zack Barresse

Okay, I had the formula, but the syntax was wrong for some reason. Here is a working formula ...

=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))

Where A1 holds your 123 value, or whatever.

Enjoy.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On