title: (C# - Excel) How do I write a formula to a newly created column for each existing row?
body: <p>Say you have an excel file with a bunch of numbers and rows, about 100 rows.</p>
  
  <p>I want to programatically write a column with a formula that adds the values of a bunch of fields together for each existing row.</p>
  
  <p>So first of all, of course, I would create the Openfiledialog for the Excel file, find the path, open it up through OleDb. Got this down. Now say I have the following table:</p>
  
  <p>[<strong>Friends | Money | Money Lost | Days passed | Solution</strong>]</p>
  
  <p>Bilbo   ,      50        ,   50          ,   7        ,       *Formula here</p>
  
  <p>Bilso   ,  80    ,   50      ,     7     , *Formula here</p>
  
  <p>etc...</p>
  
  <p>Problem is, I don't have an exact number for the rows so I'll have to enumerate whatever is found by the OleDb connection.
  The formula would be something like =(B2-C2)*D2 but 2 is also wrong and it would have to be relative to whatever row it's on, ie: row 3 would be =(B3-C3)*D3. I'm not sure how to do this.</p>
  
  <p>I found out writing directly to the cell also doesn't make the formula process the numbers outright. </p>
  
  <p>=====</p>
  
  <p>EDIT:</p>
  
  <pre><code>    private Excel.Application Xls;
      private Excel.Workbooks WBs;
      private Excel.Workbook WB;
      private Excel.Worksheet WS;
      private Excel.Sheets SS;
      Excel.Range cellsRange = null;
      Excel.Range columnRange = null;
      Excel.Range rowRange = null;
      int numberOfColumns = 0;
      int numberOfRows = 0;
      private void btnColumn_Click(object sender, EventArgs e)
      {
          if (btnColumn.FlatStyle == FlatStyle.Flat)
              btnColumn.FlatStyle = FlatStyle.Standard;
          else
          {
              btnColumn.FlatStyle = FlatStyle.Flat;
          }
  
          OpenFileDialog openFileDialog = new OpenFileDialog();
          openFileDialog.Filter = "Excel Files|*.xls;*.xlsx";
          openFileDialog.ShowDialog();
          string pather = openFileDialog.FileName;
  
          if (pather == "" || pather == " " || pather == null)
          {
              return;
          }
  
          if (!string.IsNullOrEmpty(openFileDialog.FileName))
          {
              try
              {
                  Xls = new Excel.Application();
                  WBs = Xls.Workbooks;
                  WB = WBs.Open(pather, 0, false, 5, "", "", true,
                      XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                  SS = WB.Worksheets;
                  WS = SS.get_Item(1);                    
                  cellsRange = WS.Cells;
                  columnRange = cellsRange.Columns;
                  rowRange = cellsRange.Rows;
                  numberOfColumns = columnRange.Count;
                  numberOfRows = rowRange.Count;
                  int LastCell = numberOfColumns+1;
                  WS.Cells[1, LastCell] = "Tax Formula";
                  for (int i = 2; i &lt; numberOfRows; i++)
                  {
                      //string niceFormula = "=SUM(L" + i + ",M" + i + ")*N"+ i ;
                      //WS.Cells[i, LastCell].Formula = niceFormula;
                      WS.Cells[i, LastCell].Value = (WS.Cells[i, 12] + WS.Cells[i, 13]) * WS.Cells[i, 14];
                  }
                  //==========================
                  WB.Save();
              }
              catch (Exception ex)
              {
                  MessageBox.Show("Write Excel: " + ex.Message);
              }
              finally
              {
                  GC.Collect();
                  GC.WaitForPendingFinalizers();
                  WB.Close();
                  Xls.Quit();
  
                  releaseObject(cellsRange);
                  releaseObject(columnRange);
                  releaseObject(rowRange);
                  releaseObject(SS);
                  releaseObject(WS);
                  releaseObject(WBs);
                  releaseObject(WB);
                  releaseObject(Xls);
              }
          MessageBox.Show("Finished Updating File", "Task complete");
      }
  }
  </code></pre>
  
  <p>Anyone know why this code is throwing the following error on write attempt? </p>
  
  <p>HRESULT: 0x800A03EC</p>
  
  <p>Repasted entire code for your convenience. It's still spitting out the HRESULT error. </p>
  
  <p>Targeted item is 4 rows deep with 1 row of headers and about 16 columns wide.</p>