Error 1004 - unable to set the FormulaArray property of the Range

[diepvic]

Hi,

I've got a macro like below:

Rang("A1").FormulaArray = _
"=+U24-SUM(IF(('[Data process.xls]Sundry-LEC'!$J$1:$J$9=$A$24)*('[Data
process.xls]Sundry-LEC'!$F$1:$F$9=$B$6),'[Data
process.xls]Sundry-LEC'!$G$1:$G$9,0))-SUM(IF((('[Data process.xls]BS (excl.
Off PL)'!$L$1:$L$" & lngLastRowBS & "=""1305028050"")+('[Data process.xls]BS
(excl. Off PL)'!$L$1:$L$" & lngLastRowBS & "=""1305028055""))*('[Data
process.xls]BS (excl. Off PL)'!$M$1:$M$" & lngLastRowBS & "=$B$6),'[Data
process.xls]BS (excl. Off PL)'!$N$1:$N$" & lngLastRowBS & ",0))"

Yep, the formula for A1 is so complex & quite long. Not sure if that's the
reason which causes error 1004. Pls help me how to solve it.

 

[JLatham]

One thing I see right away is that there is no such thing as "Rang("
worksheets have a Range( object.

Also, on down about the 4th row that shows up here is a segment that looks
like
lngLastRowBS & "=""1305028050"")+(
That probably won't come out right - I think it should probably be
lngLastRowBS & "=1305028050")+(
or else it needs some & symbols in it to pull things together.

A way to see what it is building it to assign it all to a string and either
put the string into a cell and examine it and compare it to what it should
be, or to display it in a message box for examination (not the best way).

Try starting with this:
Dim myFormula as String
myFormula = _
"=+U24-SUM(IF(('[Data process.xls]Sundry-LEC'!$J$1:$J$9=$A$24)*('[Data
process.xls]Sundry-LEC'!$F$1:$F$9=$B$6),'[Data
process.xls]Sundry-LEC'!$G$1:$G$9,0))-SUM(IF((('[Data process.xls]BS (excl.
Off PL)'!$L$1:$L$" & lngLastRowBS & "=""1305028050"")+('[Data process.xls]BS
(excl. Off PL)'!$L$1:$L$" & lngLastRowBS & "=""1305028055""))*('[Data
process.xls]BS (excl. Off PL)'!$M$1:$M$" & lngLastRowBS & "=$B$6),'[Data
process.xls]BS (excl. Off PL)'!$N$1:$N$" & lngLastRowBS & ",0))"

Range("A1") = "'" & myFormula
(that is a single quote mark between 2 double quote marks to put the single
quote mark in front of the = of the formula so that it will be visible as a
text entry on the sheet for comparison).

 

[Charles Williams]

Your formula is too long: using VBA to insert a FormulaArray the max is
approx 256, your formula is over 470.

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[diepvic]

Thx JLatham,
The' Rang(" is my typo.

the 4th line: lngLastRowBS & "=""1305028050"")+(
double " is because 1305028050 is a text, not a number. So in excel, in a
formula, i need to quote it to make it a text.

I'll try like what you suggested. But, I do not really understand.
the formula works well in excel. i just copy it to macro and change the
number of the last row in to "lngLastRowBS" which is my variable used to find
the last used row after refresh data.

 

[Dave Peterson]

When I have trouble writing a formula to a cell in the worksheet, I'll make it a
string.

.....formulaarray = "U24-...

I removed the =+ (in fact, the + isn't necessary).

Then let the macro get by that line and back to the worksheet that got the
formula.

Add the = sign back and hit ctrl-shift-enter (since you want an array formula).

Maybe that'll give you a hint what's wrong.

ps. Make sure you're in A1 reference style, too.

Hi,

I've got a macro like below:

Rang("A1").FormulaArray = _
"=+U24-SUM(IF(('[Data process.xls]Sundry-LEC'!$J$1:$J$9=$A$24)*('[Data
process.xls]Sundry-LEC'!$F$1:$F$9=$B$6),'[Data
process.xls]Sundry-LEC'!$G$1:$G$9,0))-SUM(IF((('[Data process.xls]BS (excl.
Off PL)'!$L$1:$L$" & lngLastRowBS & "=""1305028050"")+('[Data process.xls]BS
(excl. Off PL)'!$L$1:$L$" & lngLastRowBS & "=""1305028055""))*('[Data
process.xls]BS (excl. Off PL)'!$M$1:$M$" & lngLastRowBS & "=$B$6),'[Data
process.xls]BS (excl. Off PL)'!$N$1:$N$" & lngLastRowBS & ",0))"

Yep, the formula for A1 is so complex & quite long. Not sure if that's the
reason which causes error 1004. Pls help me how to solve it.

 

[diepvic]

Thanks you guys for your support.
I've been advised a macro as below:

Dim myFormula1 as string
Dim myFormula2 as string

myFormula1 = " +U24-SUM(IF(('[Data
process.xls]Sundry-LEC'!$J$1:$J$9=$A$24)*('[Data
process.xls]Sundry-LEC'!$F$1:$F$9=$B$6),'[Data
process.xls]Sundry-LEC'!$G$1:$G$9,0))" & "+1"

myFormula2 = "-SUM(IF((('[Data process.xls]BS (excl.
Off PL)'!$L$1:$L$" & lngLastRowBS & "=""1305028050"")+('[Data process.xls]BS
(excl. Off PL)'!$L$1:$L$" & lngLastRowBS & "=""1305028055""))*('[Data
process.xls]BS (excl. Off PL)'!$M$1:$M$" & lngLastRowBS & "=$B$6),'[Data
process.xls]BS (excl. Off PL)'!$N$1:$N$" & lngLastRowBS & ",0))"

With ActiveSheet.Range("A1")
.FormulaArray = myFormula1
.Replace "+1",myFormula2
End with

 

[Dave Peterson]

I didn't notice the length of the string.

If you want more info, take a look at Dick Kusleika's site:
http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

It's a technique to workaround that .formulaarray length limit.

Thanks you guys for your support.
I've been advised a macro as below:

Dim myFormula1 as string
Dim myFormula2 as string

myFormula1 = " +U24-SUM(IF(('[Data
process.xls]Sundry-LEC'!$J$1:$J$9=$A$24)*('[Data
process.xls]Sundry-LEC'!$F$1:$F$9=$B$6),'[Data
process.xls]Sundry-LEC'!$G$1:$G$9,0))" & "+1"

myFormula2 = "-SUM(IF((('[Data process.xls]BS (excl.
Off PL)'!$L$1:$L$" & lngLastRowBS & "=""1305028050"")+('[Data process.xls]BS
(excl. Off PL)'!$L$1:$L$" & lngLastRowBS & "=""1305028055""))*('[Data
process.xls]BS (excl. Off PL)'!$M$1:$M$" & lngLastRowBS & "=$B$6),'[Data
process.xls]BS (excl. Off PL)'!$N$1:$N$" & lngLastRowBS & ",0))"

With ActiveSheet.Range("A1")
.FormulaArray = myFormula1
.Replace "+1",myFormula2
End with