excel formula array

Question

hi i have a c# program which gives the excel2007 range, its formulaarray as follows

   Excel.Worksheet ws_res = (Excel.Worksheet)
                      wb.Worksheets.Add(mis, mis, mis, mis);
   Excel.Range range_res = (Excel.Range)ws_res.get_Range("A1","HM232");
   range_res.FormulaArray = "=(IF((IF(Sheet4!A1:HM232=1,0,"+
                     "IF(Sheet4!A1:HM232=0,1,Sheet4!A1:HM232)))=1,0,"+
                     "IF((IF(Sheet4!A1:HM232=1,0,"+
                     "IF(Sheet4!A1:HM232=0,1,Sheet4!A1:HM232)))=0,1,("+
                     "IF(Sheet4!A1:HM232=1,0,"+
                     "IF(Sheet4!A1:HM232=0,1,Sheet4!A1:HM232))))))";

it gives me exception saying that formula is wrong... but if open excel-2007 and in a new sheet (let's say sheet5) select the range A1:HM232 and paste the above formula directly to the formula bar, and then press the Ctrl+Shift+Enter together it does everything fine,... plz can you tell me how to do the same with the c#?

i am aware that if i use formulaarray to use the R1C1 style, but if i use the

              "=ROUND((IF(Sheet4!A1:HM232=1,0,"+
                 "IF(Sheet4!A1:HM232=0,1,Sheet4!A1:HM232))),0)"

it gives me no exceptions and it performs it as if i did Ctrl+Shift+Enter, both from c# and excel directly

for the above two formulas i did change the A1:HM232 to R1C1:R232C221 again shorter one works fine from c# but the longer one does not!

Answer 1

Just off the top of my head, did you mean to apply the formula to the entire range?

EDIT: Try just setting the Formula property instead of the FormulaArray property.

Answer 2

It looks like you need to modify the cell references. From the FormulaArray documentation:

If you use this property to enter an array formula, the formula must use the R1C1 reference style, not the A1 reference style.

More info on R1C1 reference style:

• How to Switch between A1 and R1C1 Reference Style in Excel?
• What are A1 and R1C1 Reference Style in Excel?

Answer 3

I think you need to specify the first cell for the formula, even though you are applying the formula over the entire selection:

range_res("A1").FormulaArray = "=(IF((IF(Sheet4!A1:HM232=1,0,"+ etc

Answer 4

Try using a sumproduct instead. It has the advatage of being an "implicit" array formula.

myrange.formula = "=sumproduct((logicalExpr1) * (logicalExpr2) * (logicalExpr3))

It will also make your formula more readable.

Answer 5

The issue is that Formula Array doesn't seem to work with R1C1 referencing because now we have an RC column and it confuses Excel as to which formula style to apply to the formulaArray let property

=Sum(RC2:RC3) is both RC format and a A1 reference format now.

And they haven't added a new let property like formulaArrayR1C1

Good work Microsoft!! idiots

my 2c

Robert J

Answer 6

yep I agree with Robert J. so you have to convert one of the formulas first (preferably to A1). luckily, there is a function to do that but then you have to run the macro every time, instead of just entering in the cell using Ctrl-Shift-Enter.

Answer 7

You can still enter formulas in the R1C1 format. Try it this way:

Private Sub Tryformula() Range("B2").FormulaR1C1 = "=RC[-1]+R4C4" End Sub