From what you said originally, I thought to suggest the array formula you
cite. But, your point about end users is well taken, unless you protect or,
as we do in TEXAS, just shoot em.

--
Don Guillett
SalesAid Software

"JohnJack" wrote in message
ups.com...
In quattro pro you can have a formula @sum(@abs(Range)) that will give
you the sum of the absolute values of every cell in that range. In
excel, the only way to do this is to do sum(abs(range) and use the
ctrl-shift-enter when applying the formula, which turns into
{=sum(abs(range)}. The problem is is that a user will often click in
the formula bar to check what range is being selected, and if they are
unaware that they have to hit ctrl-shift-enter the output from that
formula is now incorrect. To avoid having to have a 2nd column with
the individual absolute values and suming that column or running the
risk of a user messing up the formula (protection of the cell isn't a
viable solution in this case), I'm writing this function. If there is
a easier way, I'm all ears, but everywhere I've looked I haven't found
a function that is as good as the quattros @sum(@abs(range)).


On Feb 14, 12:20 pm, "Don Guillett" wrote:
I don't understand what you are trying to do?

--
Don Guillett
SalesAid Software
"JohnJack" wrote in
message

oups.com...

Hello everyone,

Below is my function I am trying to write. The problem I am running
into is that I cannot figure out what line of code will allow me to
exit the loop once y0(1,A) = <subscript out of range, ie there are no
more columns in the range. Can anyone point out how to trap/exit when
this error occures so that I can exit and the A value is the number of
columns in the range?

Can anyone help me out here?

Cheers,

Jack

Function SumAbs(y0 As Range)
'this function will return the sum absolute value from all the cells
in the range provided

Dim A, B, Counter,Counter2 As Integer

A = 1
Do While y0(1, A) < ""
A = A + 1
Loop
A = A - 1

B=Application.Count(y0)/A ' this part determines how many rows are
in the y0 range by dividing the total count by the amount of columns
(A)

SumAbs=0

For Counter2= 1 to A
For Counter = 1 To B

SumAbs = SumAbs + Abs(y0(Counter, Counter2))

Next Counter
Next Counter2

End Function