Copying A Formula--And applying it to a new cell r

Question

So here's what I am going through. I am using the Excel dll with c# in order to go inside a big and nasty excel sheet so that others don't have to.

We have a formula in one cell that is rather large and we don't want to copy it to every row because of this. This formula uses multiple values on the row that it is placed on. If it is on row 1, it uses lots of cells from that row.

When one copies this formula normally in excel, the new ranges of the cells are modified to reflect the new starting position.

The problem is that when I copy the formula like this, it still gives me all of the values that have to do with the first row instead of the row where I pasted it.....Here is my code:

  sheet.Cells[77][row].Formula = sheet.Cells[77][1].Formula;

Can somebody let me know how to make the formula actually apply to the new row instead of row 1?

Answer 1

Hi guys m posting this because this code is used to copy the formula behind a cell in Excel:

public void copy_Formula_behind_cell()
{
    Excel.Application xlapp;
    Excel.Workbook xlworkbook;
    Excel.Worksheet xlworksheet;
    Excel.Range xlrng;
    object misValue = System.Reflection.Missing.Value;

    xlapp = new Excel.Application();
    xlworkbook =xlapp.Workbooks.Open("YOUR_FILE", 0, true, 5, "", 
    "",true,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", 
    false, 
    false, 0, true, 1, 0);

    xlworksheet = (Excel.Worksheet)xlworkbook.Worksheets.get_Item(1);

    string sp = xlworksheet.Cells[3,2].Formula;//It will Select Formula using Fromula method//

    xlworksheet.Cells[8,2].Formula = 
    xlapp.ConvertFormula(sp,XlReferenceStyle.xlA1, 
    XlReferenceStyle.xlR1C1, XlReferenceType.xlAbsolute,
    xlworksheet.Cells[8][2]);
    //This is used to Copy the exact formula to where you want//

    xlapp.Visible = true;
    xlworkbook.Close(true, misValue, misValue);
    xlapp.Quit();

    releaseObject(xlworksheet);
    releaseObject(xlworkbook);
    releaseObject(xlapp);
}


private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Unable to release the Object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}

Answer 2

This will probably work, as it works from VBA... in most cases.

sheet.Cells[77][row].FormulaR1C1 = sheet.Cells[77][1].FormulaR1C1;

This would work because FormulaR1C1(not a very informative link) uses R1C1 notation which describes the referenced cells location in relation to the current cell instead of saying which cells to use. This means the actual references are dependent on the cell with the formula. When you just use Formula, you're copying the string of the Formula exactly including the hard coded cell references.

Answer 3

You could use Application.ConvertFormula

So, let's say my Cell = Cells77 has a formula that says =Sum(B77,C77) (Cells from the same row).
if want to copy it to a cell right below it, you would do something like:

string formula = Sheet1.Cells[77][2].Formula;
Sheet1.Cells[77][2].Formula = app.ConvertFormula(formula, XlReferenceStyle.xlA1, XlReferenceStyle.xlR1C1, XlReferenceType.xlRelative, Sheet1.Cells[77][3]);   

Full console app that works (You need to modify cells though).

 static void Main(string[] args)
        {
           var app = new Microsoft.Office.Interop.Excel.Application();

                var workbook = app.Workbooks.Open(@"C:\Users\user\Desktop\Book1.xlsx");
                Microsoft.Office.Interop.Excel.Worksheet Sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item("Sheet1");
                string formula = Sheet1.Cells[5][3].Formula;
                Sheet1.Cells[5][4].Formula = app.ConvertFormula(formula, XlReferenceStyle.xlA1, XlReferenceStyle.xlR1C1, XlReferenceType.xlRelative, Sheet1.Cells[5][3]);
                workbook.SaveAs(@"C:\Users\user\desktop\test.xlsx");
                workbook.Close();
        }   

You can modify third and forth parameter of ConvertFormula method to your liking. Read more about the method here: ConvertFormula.
If you want to stretch formula accross multiple rows, you can try to use range.AutoFill()