Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

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

    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
    [MS MVP - Word]

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  4. #4
    Uranium Lounger
    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, SR-2)

    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

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Works great! Thanks!

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  7. #7
    Uranium Lounger
    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, SR-2)

    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

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •