Formula Length
 

I had a Grand total formula which read like:

cell.value = "=Sum(" & myrange & ")"

which generated the following cell text.

=Sum(A1,A11,A21,...)

However, this is very limited in the number of cells it
can contain.

I've now updated it to the following format.

=Sum(A1 + A11 + A21 + ....)

I believe this format is much larger than the previous
format, however, still does have a limit? Is this belief
correct?

Is there anyway around this limit other than to compute
the total in VBA and write the value to the cell?

David

Formula Length
 

Formulas are restricted to a length of 1024 characters when the formula is
expressed in R1C1 format.

(in otherwords, the length of the formula is as measured when it is
expressed in R1C1 format).

=Sum(a1+a2) is redundant.

either
=Sum(a1,A2)

or
=A1+A2

If there are no numbers in the contiguous range that you want to add, then
you could just use

=Sum(A1:A2000)

non numeric values will be ignored (except errors).

if you have a recuring interval such as you illustrate (1,11,21 . . .

you could use an array formula like this:

=SUM(IF(MOD(ROW(A1:A50000),10)=1,A1:A50000))

entered with ctrl+Shift+enter rather than enter

--
Regards,
Tom Ogilvy


"David Fixemer" wrote in message
...
I had a Grand total formula which read like:

cell.value = "=Sum(" & myrange & ")"

which generated the following cell text.

=Sum(A1,A11,A21,...)

However, this is very limited in the number of cells it
can contain.

I've now updated it to the following format.

=Sum(A1 + A11 + A21 + ....)

I believe this format is much larger than the previous
format, however, still does have a limit? Is this belief
correct?

Is there anyway around this limit other than to compute
the total in VBA and write the value to the cell?

David