Is there a limit on Formula length in a macro? Or is this a bug?

Posted by John F on February 19, 2001 8:49 AM

I'm trying to sum a whole bunch of cells in part of a macro
"Run-time error'1004': Application-defined or object defined error"
The first line of code objected to is:

ActiveCell.Formula = "=SUM D9:D12,D14:D15,D17,D21,D31,I9:I12,I14:I15,I17,I21,I31,L9:L12,L14:L15,L17,L21,L31,R9:R12,R14:R15,R17,R21,R31,X9:X12,X14:X15,X17,X21,X31,Y9:Y12,Y14:Y15,Y17,Y21,Y31,Z9:Z12,Z14:Z15,Z17,Z21,Z31,AA9:AA12,AA14:AA15,AA17,AA21,AA31,AD9:AD12,AD14:AD15,AD17,AD21,AD31,AE9:AE12,AE14:AE15,AE17,AE21,AE31,AF9:AF12,AF14:AF15,AF17,AF21,AF31,AN9:AN12,AN14:AN15,AN17,AN21,AN31)"

If I break the formula in two and then sum the results, it works fine. But I really dont want to have to do this for all the similar lines in the macro if I can avoid it.
Any ideas what's going wrong?

Posted by Aladin Akyurek on February 19, 2001 11:24 AM

Your SUM-formula obviusly exceed the limit (the string length) Excel can process.

Aladin

Posted by Celia on February 19, 2001 3:29 PM

John
The following is one way of doing it :-

Dim rng1 As Range, rng2 As Range
Set rng1 = [D9:D12,D14:D15,D17,D21,D31,I9:I12,I14:I15,I17,I21,I31,L9:L12,L14:L15,L17,L21,L31,R9:R12,R14:R15,R17,R21,R31,X9:X12,X14:X15,X17,X21,X31,Y9:Y12,Y14:Y15,Y17,Y21,Y31]
Set rng2 = [Z9:Z12,Z14:Z15,Z17,Z21,Z31,AA9:AA12,AA14:AA15,AA17,AA21,AA31,AD9:AD12,AD14:AD15,AD17,AD21,AD31,AE9:AE12,AE14:AE15,AE17,AE21,AE31,AF9:AF12,AF14:AF15,AF17,AF21,AF31,AN9:AN12,AN14:AN15,AN17,AN21,AN31]
ActiveCell.Value = Application.Sum(rng1, rng2)

Celia

Posted by Dave Hawley on February 19, 2001 4:50 PM

John, FYI, yes there is a limit and it's 30 numbers or ranges.

Dave
OzGrid Business Applications