Results 1 to 8 of 8

20020813, 01:09 #1
 Join Date
 Mar 2001
 Posts
 167
 Thanks
 0
 Thanked 0 Times in 0 Posts
Update formula for inserted row (97, SR2)
I'm using this formula in Column E of a worksheet to keep a running total which is increased by an entry in C and decreased by an entry in D: =IF(AND(C2="",D2=""),"",E1+C2D2). I've filled the formula down in Column E far enough to give me plenty of space for entries in any given accounting period. Occasionally I need to insert a row somewhere among the completed entries. How can I alter this formula to get Excel to insert it in Column E of the new row and update all the following values in Column E?
Clark

20020813, 02:32 #2
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,175
 Thanks
 2
 Thanked 455 Times in 374 Posts
Re: Update formula for inserted row (97, SR2)
Hi Clark,
If you just want to be able to copy your formula into the new row, without having to update the row below, you could use:
=IF(AND(C2="",D2=""),"",OFFSET(E2,1,0)+C2D2)
However, if you want Excel to automatically insert the formula into the new row as well, you'll need an eventdriven macro that fires every time you insert a new row into a defined range.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20020817, 14:44 #3
 Join Date
 Mar 2001
 Posts
 167
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Update formula for inserted row (97, SR2)
Thanks very much. Would you or someone else be willing to write such a macro for me?
Clark

20020817, 22:44 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Update formula for inserted row (97, SR2)
Something like this should do it. It will insert a row above the row which contains the selected cell and then fix up the formula in column E.
<pre>Sub InsertRow()
Dim lRow As Long
lRow = Selection.Row  1
With ActiveSheet
Selection.EntireRow.Insert
Range("E1").Offset(lRow  1, 0).AutoFill _
Destination:=Range(.Range("E1").Offset(lRow  1, 0), .Range("E1").Offset(lRow + 1, 0)), _
Type:=xlFillDefault
End With
End Sub
</pre>
Legare Coleman

20020820, 12:14 #5
 Join Date
 Mar 2001
 Posts
 167
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Update formula for inserted row (97, SR2)
Works great! Thanks!

20030201, 21:58 #6
 Join Date
 Mar 2001
 Posts
 167
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Update formula for inserted row (97, SR2)
This macro works very well for me. However, sometimes I would like to delete an entire row because it's a duplicate entry or for some other reason. This messes up the balance in every subsequent row. My only recourse is to leave the incorrect entry and change its value to 0. Do I need a "delete row" macro now?
Clark

20030202, 01:54 #7
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Update formula for inserted row (97, SR2)
The VBA routine below should delete the row containing the active cell and then fix up the formulas in column E.
<pre>Public Sub DelCurRow()
Dim oCell As Range, lLastRow As Long
lLastRow = ActiveSheet.Range("E1").Offset(ActiveSheet.UsedRan ge.Row + _
ActiveSheet.UsedRange.Rows.Count, 0).End(xlUp).Row
If ActiveCell.Row <= 2 Then Exit Sub
Set oCell = ActiveSheet.Range("E1").Offset(ActiveCell.Row  2, 0)
ActiveCell.EntireRow.Delete
oCell.AutoFill Destination:=Range(oCell, _
ActiveSheet.Range("E1").Offset(lLastRow  1, 0)), _
Type:=xlFillDefault
End Sub
</pre>
Legare Coleman

20030202, 03:02 #8
 Join Date
 Mar 2001
 Posts
 167
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Update formula for inserted row (97, SR2)
Thanks again!