Adding Numbers in a Cell

[kwedde01]

How can I add several numbers in a cell.

For example: Cell A1 would contain the numbers 5,10,6,12,etc.

Each comma represents the end of a number and the start of a new one. How would I then add these 4 numbers in the same cell.

 

[just_jon]

I get an error message of #NAME when i try the Test function.

I try entering 2,3 in cell A1 on a new sheet, and I place the formula in cell b1 but get that error messages

Any suggestions

Yu have to copy the function above to a standard module [ module1, etc ] in your workbook in order for it to work.

 

[kwedde01]

Great, works perfectly

Thanks everyone

 

[kwedde01]

How can I modify the code so that it will also, read cells with only one entry and no comma, or count cells with no entry as 0.

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> Test(Rng <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Rng.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN>
        Test = "Error: single cell ref only"
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
    <SPAN style="color:#00007F">ElseIf</SPAN> Len(Rng) = 0 <SPAN style="color:#00007F">Then</SPAN>
        Test = vbNullString
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Test = Evaluate(Application.Substitute(Rng, ",", "+"))
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>