Extend Formula Range Using Excel Interop

I'm using the Excel Interop libraries to take 开发者_Python百科a sheet and replace values in the sheet. My sheet structure is as follows:

Columns A-E contain formula which work off the data in Columns F-L When I update the sheet, I clear the contents from F-L and leave any formula in A-E. I then fill columns F-L with new data.

I need my formula in Columns A-E(any rows) to extend to the full range of the new data I entered.

For example =sum(G1,G8) should become =sum(G1,G20) if I extend the data to 20 rows from 8. Is there a way to get to all present formula in the sheet? How do I extend the range of the formula?

While inputting data to the cells, keep track of each new cell and then apply the formula to that range:

sheet.Cells[rowCount, column].Formula = string.Format("=SUM(G1:G{0})", rowCount);

For information on manipulating the Excel object mode, I'd recommend looking first for a VBA solution, then converting this to C# (which is trivial). There are far more VBA resources available on the web, and you can sometimes even help yourself by recording a VBA macro.

Depending on exactly where your formulae are, you may be able to achieve what you want by inserting rows.

E.g. if you have a formula in, say, A3 that references the range G1:G8, and you now want to insert twenty rows of data instead of 8, you can move row 8 down 20-8 = 12 times as follows before writing your data.

Dim rng As Range
Dim nIndex As Long
Set rng = Rows("8:8")
For nIndex = 1 To 20 - 8
    rng.Insert Shift:=xlDown
Next nIndex

When you do this, the formula in A3 will be modified automatically to include the inserted rows.

Note that doing this via Interop will not be particularly efficient for a large number of rows (a round trip from your app to the Excel process for each row inserted). One way to improve performance is to insert multiple rows at a time.

I had a similar problem, but with IF formula. Setting it to Formula property didn't work. However the same works if set to FormulaLocal...

thanks Microsoft :)

btw: Formula and FormulaLocal will work only for A1 reference style. For R1C1 you should use FormulaR1C1 and FormulaR1C1Local.

You may have to parse the object to Range...

(((Excel.Range))yourWorkSheet.Cells[rowCount, column]).Formula = string.Format("=SUM(G1,G{0})", rowCount);