vba 如何复制一行数据，并用偏移量粘贴它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15573778/
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
How can I copy a row of data, and paste it with an offset
提问by Pedro
I'm working on a Excel 2010 Sheet that has some doctors names and their adresses, but frequently there are 2 names that are identical but have diferent adresses. On this cases I would like to copy the adress info to the same row as the first name but wit h an offset of 4 collumns. Heres the code I came up with
我正在处理一个 Excel 2010 工作表，其中包含一些医生的姓名及其地址，但通常有 2 个名称相同但地址不同。在这种情况下，我想将地址信息复制到与名字相同的行，但偏移量为 4 列。这是我想出的代码
Sub OraganizadorEndere?os()
ActiveCell.Select
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value _
Then ActiveCell.Offset(1, 0).Activate: _
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 4)).Copy: _
ActiveCell.Offset(-1, 0).Select: _
ActiveCell.Offset(0, 5).Paste _
Else ActiveCell.Offset(1, 0).Select
End Sub
But I get an error on the
但我收到一个错误
ActiveCell.Offset(0, 5).Paste _
Else ActiveCell.Offset(1, 0).Select
Part of the code, saying that the obeject does not accept this property/method
部分代码，说对象不接受这个属性/方法
And remember, I started programing in VBA today, so if you can answer with an explanation, I would appreciate.
记住，我今天开始用 VBA 编程，所以如果你能回答一个解释，我将不胜感激。
回答by MattCrum
Try to rely less on activating and selecting cells - you can assign cells to a range variable to make things much easier. Also, you don't need to copy the cells (unless you also want to copy the formatting e.g. colours), use their .Value instead:
尽量减少对激活和选择单元格的依赖——您可以将单元格分配给一个范围变量，使事情变得更容易。此外，您不需要复制单元格（除非您还想复制格式，例如颜色），而是使用它们的 .Value ：
Sub OraganizadorEndere?os()
Dim rngTest as Range 'Define rngTest variable as Range
Set rngTest = Activecell 'Set rngTest to be the ActiveCell
If rngTest.Value = rngTest.Offset(1, 0).Value Then
'Replace the .Value of the columns to right with the .Value of the row below
Range(rngTest.Offset(0,5), rngTest.Offset(0,8).value = Range(rngTest.Offset(1, 1), rngTest.Offset(1, 4)).Value
Else
Set rngTest = rngTest.Offset(1,0) 'Set rngTest to be the next line down
End If
End Sub
回答by Pedro
Try below code :
试试下面的代码：
Sub OraganizadorEndere?os()
Dim rng As Range
Dim offsetRng As Range
Set rng = ActiveCell
rng.Select
Set offsetRng = rng.Offset(1, 0)
If rng = offsetRng Then
offsetRng.Offset(0, 1).Resize(, 4).Copy offsetRng.Offset(0, 5)
rng.Offset(1, 0).Activate
Else
rng.Offset(1, 0).Select
End If
End Sub
