Answered by:
Exception:Unable to set the FormulaArray property of the Range class
Question

The following codes raises an exception
Range rng = ((Worksheet)applicationObject.ActiveWorkbook.Sheets["收支记录"]).get_Range("I2"); rng.FormulaArray = "=SUM(IF(INDIRECT(\"收支记录!R2C7:R\"&SUM(ROW())&\"C7\",FALSE)=INDIRECT(\"收支记录!R\"&SUM(ROW())&\"C7\",FALSE),INDIRECT(\"收支记录!R2C3:R\"&SUM(ROW())&\"C3\",FALSE),0),IF((转账记录!R2C3:R65536C3=INDIRECT(\"收支记录!R\"&SUM(ROW())&\"C7\",FALSE))*(转账记录!R2C1:R65536C1<=INDIRECT(\"收支记录!R\"&SUM(ROW())&\"C1\",FALSE)),转账记录!R2C2:R65536C2,0),IF((转账记录!R2C4:R65536C4=INDIRECT(\"收支记录!R\"&SUM(ROW())&\"C7\",FALSE))*(转账记录!R2C1:R65536C1<=INDIRECT(\"收支记录!R\"&SUM(ROW())&\"C1\",FALSE)),转账记录!R2C2:R65536C2,0),IF(账户信息!R2C3:R65536C3=INDIRECT(\"收支记录!R\"&SUM(ROW())&\"C7\",FALSE),账户信息!R2C4:R65536C4,0))";
But if i copy and paste the above formulas to the formula bar of Excel 2010 and press Ctrl+Shift+Enter, it work perfectly!
I used Range.FormulaArray elsewhere in my program and it worked fine. Maybe someone can point out the problem.
Answers

Hi Sirius,
Please note that FormulaArray property has a character limit of 255. How many characters does your formula contains?
Did you try to set simple values to be sure that you can set the property at all? For example:
Worksheets("Sheet1").Range("A1:C5").FormulaArray = "=3"
 Marked as answer by Sirius Lee Friday, December 6, 2013 9:04 AM
All replies



Hi Sirius,
Please note that FormulaArray property has a character limit of 255. How many characters does your formula contains?
Did you try to set simple values to be sure that you can set the property at all? For example:
Worksheets("Sheet1").Range("A1:C5").FormulaArray = "=3"
 Marked as answer by Sirius Lee Friday, December 6, 2013 9:04 AM



