MS Excel Auto-Fills Incorrect Formula When Adding Rows

1
votes

I have defined a range of cells as a table in Excel 2010. I keep the sheet protected so that other users cannot mistakenly alter formulas. I've had to use a macro tied to a button to allow other users to add rows when the sheet is locked. However, I've noticed that when I add rows via the button, it auto-fills an incorrect formula in Column I. It is a formula that I typed in originally but later changed. I've also noticed that when I unlock the sheet and simply drag down the bottom-right corner of the table, it will put the correct formula in some rows and the incorrect formula in other rows, even alternating which rows have the correct formula.

Additional information you may need to answer:

  • Table includes Columns A through I
  • Current formula in Column I is: =IF(G21=0," ",IF(G21="yes",I20+D21+H21,D21+(I20-F21)))
  • Old formula no longer used in Column I: =IF(G24=0," ",IF(G24="yes",F20+H24,F20-F24))
  • The Macro used is: 
 Sub AddRow()

        ActiveSheet.Unprotect Password:="secret"

        Range("A1").End(xlDown).ListObject.ListRows.Add AlwaysInsert:=False

        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="secret"

 End Sub
7
excelexcel-formulavba
Wow! Weird! Are there hidden rows that may still contain that old formula? That could explain this. - BrettFromLA
If you can't figure it out, you could write a macro to duplicate the exact cell contents that you want into a new workbook. The macro could easily include an error trap that would alert you or something if that old formula was about to get copied over. - BrettFromLA
@BrettfromLA, there are no hidden rows. How would I write the macro to duplicate the cell contents using the macro that I already have? I currently have the macro to unlock the sheet, add a line, then lock the sheet. I believe I need to Selection.EntireRow.Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove to the code, but I'm not sure if I need anything else. Admittedly, VBA is not my strong suit. - MadeToBeMom
@MadeToBeMom I only had pseudocode in my head. Your macro would create another workbook. Then, it would use 2 nested loops to go through the rows and columns of your existing worksheet's cells. For each cell, it would take the value (which might be a formula), evaluate whether it looked like the "old" formula, and if not, put it into the same cell in the new workbook. I think that should do it. - BrettFromLA
Thank you, @BrettFromLA and @pnuts! - MadeToBeMom

7 Answers

3
votes

I had this problem as well. I selected the entire column in the Table, deleted all (new) formulas, right-clicked and selected "Clear Contents". Then recopied my new formula into the column. After doing that, when inserting a new row (using my 'row insert' macro), the new formula was autocopied into the new row. Don't know how robust that solution is but I didn't want to start fiddling with my (simple) 'row insert' macro

1
votes

Here is what worked:

  • On the Developer Tab, click Record A Macro.
  • Name the macro and entered a description, then click OK.
  • Unlock the sheet,
  • Select the Totals row,
  • Right-click and select Add A Row Above,
  • Select the last cell in Column I above the Totals row and click Ctrl+D
  • Lock the sheet. The VBA code for the macro looks like this: 

    Sub AddRowAndCopyFormula()
'
' AddRowAndCopyFormula Macro
' Add one new row and copy correct formula in Column I.
'


    '
    ActiveSheet.Unprotect
    Range("Table3[#Totals]").Select
    Selection.ListObject.ListRows.Add (17)
    Range("I24").Select
    Selection.FillDown
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True
End Sub
    Thank you to all who helped me think this through!!
    • 0
    votes

    Can you elaborate on what exactly is wrong with the formula in the inserted rows? Is it that the rows are referenced incorrectly?

    As a potential workaround could you add on this line to refill the formula?

    Range("I2:I" & Range("A1").End(xlDown).row).filldown

    Replace I2 with the first cell of the table with the formula in it

    0
    votes

    Having had the same problem, my solution was: Copy the correct formula, so you don't have to type it again and paste it somewhere safe. Delete the entire table column that has the problem. Re-insert a column back into that spot. Grab that formula and paste it back in and fill the column appropriately. This worked for me. I'm guessing that column had some formatting or some such thing

    0
    votes

    I fixed this by :

    Inserting as many new rows in the WORKSHEET at the FIRST ROW of the table as you need and fill the data in there.

    No probs anymore.

    This is a STUPID bug and should have been fixed looooong ago. A bad sign fore the future of Excel when MS doesn't care about major bugs like this.

    Dick

    0
    votes

    Similar answer to some others but I've been puling my hair out with this for the last 2 hours.

    I copied the correct formula (in my case it was in row 1) to a cell not adjacent to the table. Then deleted all the columns cells that had both the correct and incorrect formula - just using the delete key. Then copied back in the formula from the cell I previously copied to. Autofill on insert now works with the correct formula

    very weird bug....

    0
    votes

    I solved the problem with an easy way (no need to create new columns or like so):

    • Copy the formula (with the correct code).
    • Select the entire column, then click on "Home" tab, click on Clear option (Clear All).
    • Just paste the code. That's it. Worked for me.

    Hope that helped (it did the trick to me).