c# excel converting A1 formula to R1C1

Question

hi i have a excel-2007 formula written with A1 style, how is it possible to convert the A1 style formula to R1C1 in c# so that later on i can use it for range.FormulaArray=...

in documentation it says that FormulaArray should be given in R1C1 style...

for example this one

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

i want to perform not operation on a matrix, at the end i will have the 0s and 1s replaced in a matrix... in excel -2007 i would select the range and press the Ctrl+Shift+Enter!

Answer 1

I think, there are different ways to write formula.
You cannot change the A1 style formula to R1C1 style using range.Formula.

You will have to use range.FormulaR1C1, if you wish to assign formula in R1C1 notation.
What are you trying to do exactly?

Answer 2

Do you mean you want to change the way Excel does it's cell referencing programmatically?

I think you would do it this way:

Application.ReferenceStyle = xlR1C1;

After you do that you can then use range.FormulaArray in R1C1 format instead of A1.

Answer 3

Use the Application.ConvertFormula function.