How to copy a data row and paste it with an offset

Question

I am working on an Excel 2010 worksheet that has several names of doctors and their addresses, but often there are two names that are identical but have different addresses. In this case, I would like to copy the address information into the same row as the name, but taking into account 4 columns. Here is 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 

But I get an error

 ActiveCell.Offset(0, 5).Paste _ Else ActiveCell.Offset(1, 0).Select 

Part of the code, stating that the obeject does not accept this property / method

And remember, I started programming in VBA today, so if you can answer with an explanation, I would appreciate it.

Answer 1

Try to rely less on activation and cell selection — you can assign cells to a range variable to make things a lot easier. In addition, you do not need to copy cells (unless you also want to copy formatting, for example, colors), use them .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 

Answer 2

Try entering the 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