Thanks OssieMac, great idea. I will ask you some code related to this in
another post...


"OssieMac" wrote:

Hi yet again Fabian.

I didn't do well at all with this one. The function in column 40 should be

=SumAftZero(A2:AM2,6)

SumAftZero2 was an alternative method I was playing with.

Please accept my apologies for my stuff ups.

--
Regards,

OssieMac


"OssieMac" wrote:

Hi Fabian,

Yet another way is to create a UDF (User Defined Function). This allows you
to insert a function in column 40 and sum the first 6 (or whatever number of
cells you like) after the zeros.

Copy the function into a module and in column 40 (assuming you are summing
the values in row 2) insert the following

=SumAftZero2(A2:AM2,6)

A2:AM2 is the full range of 39 cells and 6 is the number of cells to sum
after the zeros. Drag the formula down.

Function SumAftZero(rngRow As Range, numbToSum As Long)
'Produces circular reference error if less cells
'left to sum than parameter numbToSum
Dim rngC As Range
numbToSum = numbToSum - 1

For Each rngC In rngRow
If rngC.Value 0 Then
Range(rngC, rngC.Offset(0, numbToSum)).Select
SumAftZero = WorksheetFunction.Sum _
(Range(rngC, rngC.Offset(0, numbToSum)))
Exit For
End If
Next rngC

End Function


--
Regards,

OssieMac