Update formula for inserted row (97, SR-2)

Topic

New Reply

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+C2-D2). 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

Reply | Quote

Replies

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)+C2-D2)

However, if you want Excel to automatically insert the formula into the new row as well, you’ll need an event-driven macro that fires every time you insert a new row into a defined range.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks very much. Would you or someone else be willing to write such a macro for me?
Clark

Reply | Quote

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.

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

Reply | Quote

Works great! Thanks!

Reply | Quote

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

Reply | Quote

The VBA routine below should delete the row containing the active cell and then fix up the formulas in column E.

Public Sub DelCurRow()
Dim oCell As Range, lLastRow As Long
    lLastRow = ActiveSheet.Range("E1").Offset(ActiveSheet.UsedRange.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

Reply | Quote

Thanks again!

Reply | Quote