vba 添加行时 MS Excel 自动填充错误的公式

MS Excel Auto-Fills Incorrect Formula When Adding Rows

提问by MadeToBeMom

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.

我在 Excel 2010 中将一系列单元格定义为表格。我保护工作表，以便其他用户不会错误地更改公式。我不得不使用与按钮绑定的宏，以允许其他用户在工作表锁定时添加行。但是，我注意到当我通过按钮添加行时，它会自动填充第一列中的错误公式。这是我最初输入但后来更改的公式。我还注意到，当我解锁工作表并简单地向下拖动表格的右下角时，它会将正确的公式放在某些行中，将不正确的公式放在其他行中，甚至交替使用具有正确公式的行。

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:

• 表包括 A 列到 I 列
• 第一列中的当前公式是： =IF(G21=0," ",IF(G21="yes",I20+D21+H21,D21+(I20-F21)))
• 第一列中不再使用的旧公式： =IF(G24=0," ",IF(G24="yes",F20+H24,F20-F24))
• 使用的宏是：

---

---

 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

回答by Damian

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

我也有这个问题。我选择了表格中的整列，删除了所有（新）公式，右键单击并选择了“清除内容”。然后将我的新公式重新复制到列中。这样做之后，在插入新行时（使用我的“行插入”宏），新公式会自动复制到新行中。不知道该解决方案有多健壮，但我不想开始摆弄我的（简单）“行插入”宏

回答by MadeToBeMom

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

选择总计行上方 I 列中的最后一个单元格，然后单击 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!!

锁定工作表。

宏的 VBA 代码如下所示：

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

感谢所有帮助我思考这个问题的人！！

回答by Silenxor

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

将 I2 替换为表格的第一个单元格，其中包含公式

回答by gavin

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

遇到同样的问题，我的解决方案是：复制正确的公式，这样您就不必再次键入并将其粘贴到安全的地方。删除有问题的整个表列。将一列重新插入该位置。获取该公式并将其粘贴回并适当填充该列。这对我有用。我猜那列有一些格式或类似的东西

回答by DickM

I fixed this by :

我通过以下方式解决了这个问题：

Inserting as many new rows in the WORKSHEETat the FIRST ROWof 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.

这是一个愚蠢的错误，应该在很早以前就修复了。当 MS 不关心像这样的主要错误时，Excel 的未来是一个坏兆头。

Dick

迪克

回答by MrPea

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

与其他人类似的答案，但在过去的 2 个小时里，我一直在用这个把头发拉出来。

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

我将正确的公式（在我的例子中它在第 1 行）复制到不与表格相邻的单元格中。然后删除所有具有正确和不正确公式的列单元格 - 只需使用删除键。然后从我之前复制到的单元格中复制回公式。插入时自动填充现在可以使用正确的公式

very weird bug....

好奇怪的bug……