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

Thread Starter
Joined
May 26, 2003
Messages
3,303
I've added some numbers and the answer is 123
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

Joined
Mar 8, 2005
Messages
19,896
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

Joined
Mar 8, 2005
Messages
19,896
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
answer = Str(ActiveCell.Value)

For count = 2 To Len(answer)
values(count - 1) = Int(Mid(answer, count, 1))
MsgBox values(count - 1)
Next count
newanswer = 0
For count = 1 To Len(answer) - 1
newanswer = newanswer + Val(values(count))
Msgbox Newanswer
Next count

ActiveCell.Offset(0, 1) = newanswer

End Sub

The message boxs are ust ther to show you what it is doing
 
Joined
Jul 25, 2004
Messages
5,458
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
 
Joined
Jul 25, 2004
Messages
5,458
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

Joined
Mar 8, 2005
Messages
19,896
Yes thanks and you?
I was on here for a while just in case there were any really desperate posters.
 
Joined
Jul 25, 2004
Messages
5,458
I had a wonderful Christmas. The kids had a great time and it made my weekend great. :D

Tell the truth, I barely got on this weekend to check my email! Playing with my presents mostly (my wife bought me a guitar). :)
 
Joined
Nov 22, 2005
Messages
869
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

Joined
Mar 8, 2005
Messages
19,896
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.
 
Joined
Jul 25, 2004
Messages
5,458
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top