Jim it works perfectly now. Thank you so much.
--
phyllis


"Jim Cone" wrote:

phyllis,
I don't fully understand the problem you have with the function.

The return value of the function is treated as a number for me.
It is right aligned in the cell. I set the cell format before entering the formula.
I tested the function result by using the its cell address in a Sum function with out a problem...
=SUM(X2:Z2)
I also tested it by calling it within a formula...
=SUM(addafterzero(A1:K1), X2, 100) - and no problem.

I am using xl2002 today on Windows XP.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"phyllis"
wrote in message
Hi Jim,
Thank you so, much. I can tell this is going to work, because whenever I
create the forumla using the function, it show the right total. However,
whenever I try use the =AddAfterZero function, excel wants to treat it like
text instead of a formula, even though I've changed the cell value to
"number". I don't understand it. I'm using MS Office 2003 version of Excel
--
phyllis


"Jim Cone" wrote:
phyllis,
Another way using a custom function...
Copy the code below into a standard module and enter this formula in a cell...

= AddAfterZero(A1:W1)

Works on first row of the specified cells.
Note: you can also select the cells instead of typing them in.
'Code starts----

Function AddAfterZero(ByRef rng As Excel.Range) As Variant
On Error GoTo BadSum
Dim N As Long
Dim dblSum As Double
Set rng = rng.Rows(1).Cells
For N = rng.Count To 1 Step -1
If rng(N) < 0 Then
dblSum = dblSum + rng(N).Value
Else
Exit For
End If
Next
AddAfterZero = dblSum
Set rng = Nothing
Exit Function
BadSum:
AddAfterZero = "Error " & Err.Number
End Function

'Code ends----
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"phyllis"
wrote in message
I need a formula that will add a string of cells until it finds a cell with a
zero, then start the adding again. For example

a b c d e f g h i j k l m n o p q r s t u v
1 8 8 8 0 8 6 5 8 9 0 6 5 4 8 7 6 0 0 5 5 5 5

So the formula would add cells a1 thru v1, but each time it encountered a
zero, ignore the previous cells and start the calucation from the next cell.
So the total for row 1 in this example would be 20, which is the total of
cells s1 thru v1
Any help you can provide is very much appreciated.
thanks
--
phyllis