InsertRow is breaking formulas


#1
  <pre class="source"><table border="0" cellspacing="0" width="100%"><tr><td width="15"></td><td bgcolor="lightgrey" width="15"></td><td bgcolor="lightgrey"><br><font face="Lucida Console, Courier" size="2"> Dim excel As Excel = New Excel excel.Open("C:\Documents and Settings\awaters\Desktop\StratTemplate_v218.xls") Dim sheet As Worksheet = excel.Worksheets("LastPayment") sheet.Cells(9, 2).PutValue(5) sheet.Cells(10, 2).PutValue(8) sheet.Cells(11, 2).PutValue(12) 'sheet.Cells.InsertRow(12) excel.Save("C:\Documents and Settings\awaters\Desktop\NewStratReport.xls")<br/></font><br> </td></tr></table><br /><br />We are using a template and inserting values into fields and occassionally an entire row. Whenever a row is inserted it breaks the formula, pointing it to the wrong cell.<br /><br />The formula is a simple percentage, D#=C#(cell to the left)/C71 )<br /> also, C71=sum(C10:C70)<br /><br />When a row is inserted, the formula changes to, D#=C#(cell to the left)/C81 )<br /> and the C81 increments, so the next line is D#=C#(cell to the left)/C82 )<br /></pre>

#2
    Dim excel As Excel = New Excel<br /> <br />         excel.Open("C:\Documents and Settings\awaters\Desktop\StratTemplate_v218.xls")<br /> <br />         Dim sheet As Worksheet = excel.Worksheets("LastPayment")<br /> <br />         sheet.Cells(9, 2).PutValue(5)<br />         sheet.Cells(10, 2).PutValue(8)<br />         sheet.Cells(11, 2).PutValue(12)<br /> <br />         '  sheet.Cells.InsertRow(12)<br /> <br />         excel.Save("C:\Documents and Settings\awaters\Desktop\NewStratReport.xls")<br /> <br /> This should look a bit better, for code.<br />

#3

Could you please post your template file here? Thank you.


#4

The file name is StratTemplate, that’s different than the code above, just as a warning. Thanks for the help.


#5

Please try this attached fix.


#6

The denominator has stopped the incrementing and has stopped randomly adding 10 to it’s self down the worksheet. The one problem remaining is when a row is incremented it isn’t adding the number of rows added to that denominator.

So the denominator is pointing to where is used to be, farther up the sheet.


#7

The denominator is C$71, right? It’s absolute reference so it should not be incremented while inserting rows. You can verify it in MS Excel. To increment it, please change the denominator to C71.


#8

Yes you are correct. I’m sorry, let me try to re-explain the problem. When a row is inserted at row 15, the denominator(C$71) is going to be pushed down by 1 row.

When using MSExcel and right clicking the row numbers, then selecting insert row, will update the formulas to point to C$72. This is where the new total is, since the insert caused everything to shift down.

Using your InsertRow function the formulas do not update. They continue to point to C$71, which is now above the Total and usually does not contain a value.


#9

Thank you for the explanation. I fixed this issue. Please try the attached fix.