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

  • Thread starter Thread starter diepvic
  • Start date Start date
D

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.
 
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).
 
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.
 
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.
Click to expand...
 
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
 
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
Click to expand...
 
Back
Top