如何复制一行数据,并用偏移量粘贴 [英] How can I copy a row of data, and paste it with an offset
问题描述
我正在使用具有一些医生姓名和地址的Excel 2010表格,但是通常有两个名称是相同的,但有不同的地址。在这种情况下,我想将地址信息复制到与名字相同的行,但是有4个列的偏移量。谢谢我提出的代码
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
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
但是我得到一个错误在
ActiveCell.Offset(0, 5).Paste _
Else ActiveCell.Offset(1, 0).Select
部分代码,表示obeject不接受此属性/方法
Part of the code, saying that the obeject does not accept this property/method
请记住,我开始在VBA中编程,所以如果你可以回答一个解释,我会很感激。
And remember, I started programing in VBA today, so if you can answer with an explanation, I would appreciate.
推荐答案
尽量减少激活和选择单元格 - 您可以将单元格分配给范围变量使事情变得更加容易。此外,您不需要复制单元格(除非您还要复制格式化例如颜色),请改用它们的.Value:
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:
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
这篇关于如何复制一行数据,并用偏移量粘贴的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!