在 excel 中添加新行而不破坏使用 Range.Value 的 vba 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10659415/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
adding new rows in excel without breaking a vba macro that uses Range.Value
提问by GideonShalev
I've written a macro in VBA that simply fills in a given cell's value from another cell in that sheet. I do this for lots of cells in the sheet, and I'm doing it like so:
我在 VBA 中编写了一个宏，它只是从该工作表中的另一个单元格中填充给定单元格的值。我对工作表中的许多单元格执行此操作，并且我正在这样做：
Range("B3").Value = Range("B200")
Range("B4").Value = Range("B201")
'etc.
Now, I am often adding values by inserting new rows, so I might insert a new row between B200 and B201, which will break the macro because it doesn't autoupdate when I insert the new row.
现在，我经常通过插入新行来添加值，所以我可能会在 B200 和 B201 之间插入一个新行，这会破坏宏，因为它在我插入新行时不会自动更新。
How can I code the macro so it autoupdates the cell references when I insert new rows or columns?
如何编码宏，以便在插入新行或新列时自动更新单元格引用？
回答by Jerry Beaucaire
My suggestion would be to make sure the ROW you want to retrieve values from has a unique value in it that you can .FIND anytime you want, then grab your values from column B of that found cell's row. So right now you want to get a value in B200 and A200 always has the text in it: "Final Total"and that is unique.
我的建议是确保您要从中检索值的 ROW 中具有唯一值，您可以随时 .FIND 查找，然后从找到的单元格行的 B 列中获取您的值。所以现在你想在 B200 和 A200 中获得一个值，其中总是有文本：“最终总计”，这是唯一的。
Dim MyRNG As Range
Set MyRNG = Range("A:A").Find("Final Total", LookIn:=xlValues, LookAt:=xlWhole)
Range("B3").Value = Range("B" & MyRNG.Row)
Range("B4").Value = Range("B" & MyRNG.Row + 1)
回答by Shiin Zu
Try giving a name to the range. If you refer to the range by name Excel searches for it and retrieves the rows that defines it. Range names update their definition when new rows are added.
尝试为范围命名。如果按名称引用范围，Excel 会搜索它并检索定义它的行。添加新行时，范围名称会更新其定义。
Adding to the above, i think this tutorial illustrates my point: http://www.homeandlearn.co.uk/excel2007/excel2007s7p6.htmlthis is how to define the name of the range.
除了以上内容，我认为本教程说明了我的观点：http: //www.homeandlearn.co.uk/excel2007/excel2007s7p6.html这是如何定义范围名称。
This tutorial explains how to use it on macros and vba: http://excel.tips.net/T003106_Using_Named_Ranges_in_a_Macro.html
本教程解释了如何在宏和 vba 上使用它：http: //excel.tips.net/T003106_Using_Named_Ranges_in_a_Macro.html
I hope this helps :D
我希望这会有所帮助：D
回答by Siddharth Rout
This is not an answer but an alternative.
这不是答案，而是替代方案。
Naming your range is the way to go as Shiin suggested but then if you have 500 cells then like I mentioned earlier, naming 500 cells and using them in your code can be very painful. The alternative is to use smart code. Let's take an example
正如 Shiin 建议的那样，命名您的范围是一种方法，但是如果您有 500 个单元格，那么就像我之前提到的那样，命名 500 个单元格并在您的代码中使用它们可能会非常痛苦。另一种方法是使用智能代码。让我们举个例子
Let's say you have a code like this
假设您有这样的代码
Sub Sample()
Range("B3").Value = Range("B200")
Range("B4").Value = Range("B201")
Range("B5").Value = Range("B201")
' And
' So On
' till
Range("B500").Value = Range("B697")
End Sub
The best way to write this code is like this
编写这段代码的最佳方式是这样的
Sub Sample()
Dim i As Long
For i = 200 To 697
Range("B" & i - 197).Value = Range("B" & i)
Next i
End Sub
and say if you insert a line at say row 300 then simply break the above code in two parts
并说如果您在第 300 行插入一行，那么只需将上述代码分为两部分
Sub Sample()
Dim i As Long
For i = 200 To 299
Range("B" & i - 197).Value = Range("B" & i)
Next i
For i = 301 To 698
Range("B" & i - 197).Value = Range("B" & i)
Next i
End Sub
So every time you insert a row, simply break the for loop into an extra part. This looks tedious but is much better than naming 500 cells and using them in your code.
因此，每次插入一行时，只需将 for 循环分成额外的部分即可。这看起来很乏味，但比命名 500 个单元格并在代码中使用它们要好得多。
If you are planning to use the macro only once (i.e for 1 time use) then read ahead.
如果您计划只使用该宏一次（即使用 1 次），请提前阅读。
If you are worried that when the user inserts the row then the cells are not updated then you can instead of assigning a value, assign a formula.
如果您担心当用户插入行时单元格不会更新，那么您可以分配一个公式而不是分配值。
For example
例如
Range("B3").Formula = "=B200"
This will put a formula =B200in cell B3. So next time when you insert a row so that the 200th row moves it's position, you will notice that the formula automatically gets updated in cell B3
这将=B200在单元格中放置一个公式B3。所以下次当你插入一行以便第 200 行移动它的位置时，你会注意到公式会自动在单元格中更新B3
HTH
HTH
