none
Excel formula s and inserting blank rows to populate the answer RRS feed

  • Question

  • Hi 

    I have formula in columns E and G. The formula will be hidden in the back ground. I want to know is there a way if someone insert a row with the date it would populate that number for the remaining of the cells that need the information. Like if I insert a row 12/8/2015 the column that has the formulas it will look at the 12/8/2015 and say 8 days. See below the second column is where the formula is located. I want to insert a new row, then insert the date and the next column it will the the math ( I put the example below and red is where i would enter the number)

    12/11/2014 0 12/11/2014 12/11/2015 Enforcement
    12/8/2014
    01/08/2015 12 01/08/2014 01/08/2016 completed
    01/08/2015 15.66666667 09/19/2013 01/08/2016 completed
    Wednesday, August 19, 2015 8:36 PM

All replies

  • Hello,

    I have a spreadsheet that is protected. I have formulas hidden in columns E and G. I want to know when a row is inserted, how do we add the formula to columns e and g in the newly inserted row(s)? Thank you please assist me 

    thank you,

    Erika
    Wednesday, August 19, 2015 4:23 PM
  • >>> I want to know when a row is inserted, how do we add the formula to columns e and g in the newly inserted row(s)?

    Could you provide more details about how to insert a row, Do you use VBA code or manual?

    If you use VBA code, you could refer to below code:

    Sub InsertRow()
         Dim rw As Long, cl As Long
         Selection.EntireRow.Insert
         n = ActiveCell.Row
         For cl = 1 To Columns.Count
             If Cells(n - 1, cl).HasFormula Then
                 Cells(n - 1, cl).Copy Cells(n, cl)
             End If
         Next
         Cells(n, 1).Select
    End Sub

    • Proposed as answer by David_JunFeng Monday, August 31, 2015 9:46 AM
    Monday, August 24, 2015 9:42 AM