vba .Range 和 .InsertShift 在新工作表上复制表格：覆盖数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15597087/
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
.Range and .InsertShift to copy a table on a new worksheet: overwriting data
提问by Avitus
I have a basic question on Sheet.Rangeand .Insert Shift:=xlDown.
I would like to import on my worksheet a table of numbers which has just 2 columns.
Every time the macro reads 2 values (on the same row) in the original table, it should copy them in columns A and B in the worksheet and continue the process by writing another pair of numbers in the subsequent row.
If I write
我有一个关于Sheet.Range和的基本问题.Insert Shift:=xlDown。我想在我的工作表上导入一个只有 2 列的数字表。每次宏读取原始表中的 2 个值（在同一行上）时，它应该将它们复制到工作表的 A 列和 B 列中，并通过在后续行中写入另一对数字来继续该过程。如果我写
Sheet.Range("A1:B1").Insert Shift:=xlDown
Sheet.Cells(1, 1) = "number 1 "
Sheet.Cells(1, 2) = "number 2"
Sheet.Range("A1:B1").Insert Shift:=xlDown
Sheet.Cells(1, 1) = "number 1 "
Sheet.Cells(1, 2) = "number 2"
all I get in the end is the last pair of values from the original table. In other words, the macro overwrites instead of moving down, row by row. What should I amend? Thanks! Avitus
我最后得到的是原始表中的最后一对值。换句话说，宏会逐行覆盖而不是向下移动。我应该修改什么？谢谢！阿维图斯
回答by chris neilsen
your code as posted apears to work, but puts the new data in in reverse order
您发布的代码似乎可以工作，但以相反的顺序放置新数据
Tested as
测试为
Sub demoAsIs()
Dim Sheet As Worksheet
Dim i As Long
Set Sheet = ActiveSheet
For i = 0 To 4
Sheet.Range("A1:B1").Insert Shift:=xlShiftDown
Sheet.Cells(1, 1) = "number " & 2 * i + 1
Sheet.Cells(1, 2) = "number " & 2 * i + 2
Next
End Sub
Sample data
样本数据
Before:
前：
After:
后：
To reverse the order, try this
要颠倒顺序，试试这个
Sub demo()
Dim Sheet As Worksheet
Dim i As Long
Dim rNewData As Range
Set Sheet = ActiveSheet
Set rNewData = Sheet.Range("A1:B1")
For i = 0 To 4
rNewData.Insert Shift:=xlShiftDown
' rNewData now refers to the one row down from where it was
rNewData.Cells(0, 1) = "number " & 2 * i + 1
rNewData.Cells(0, 2) = "number " & 2 * i + 2
Next
End Sub
Result:
结果：
Note on xlDownvs xlShiftDown
注意xlDownvsxlShiftDown
Excel VBa provides many enumeration sets for parameters. Each enumeration has an underlying numeric value. The Insertmethod, Shiftparameter uses the XlInsertShiftDirectionEnumeration: xlShiftDown, xlShiftToRight.xlShiftDown= -4121
Supplying an enumeration from another set that happens to have the same underlying numeric value (such as xlDown) will work, but is not strictly correct.
Excel VBa 为参数提供了许多枚举集。每个枚举都有一个基础数值。的 Insert方法，Shift参数使用XlInsertShiftDirection枚举：xlShiftDown，xlShiftToRight。xlShiftDown= -4121
从恰好具有相同基础数值（例如xlDown）的另一个集合中提供枚举将起作用，但并非严格正确。
Note on Insert
注意 Insert
The Shiftparameter is optional. If it is omitted, Excel decides which way to shift based on the shape of the range.
该Shift参数是可选的。如果省略，Excel 会根据范围的形状决定移动的方式。
