|
Error 1004 - unable to set the FormulaArray property of the Range
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. |
Error 1004 - unable to set the FormulaArray property of the Range
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). "diepvic" wrote: 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. |
Error 1004 - unable to set the FormulaArray property of the Range
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" wrote in message ... 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. |
Error 1004 - unable to set the FormulaArray property of the Range
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. |
Error 1004 - unable to set the FormulaArray property of the Range
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. diepvic wrote: 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. -- Dave Peterson |
Error 1004 - unable to set the FormulaArray property of the Ra
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 |
Error 1004 - unable to set the FormulaArray property of the Ra
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/arch...rmulas-in-vba/ It's a technique to workaround that .formulaarray length limit. diepvic wrote: 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 |
| All times are GMT +1. The time now is 10:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com