Move text from one cell to another in Excel IF conditions are met it 2

[burcello]

I'm trying to come up with a macro to evaluate an entire column of cells, then copy the text in the string if the conditions are met.

I have some code which does something similar. It cycles through some cells, compares each to one in a relative position, and clears the contents of other cells if the values of the first two match:

Sub CycleBlankingDuplicates()
    For counter = 1 To 1000
        Set curCell = Worksheets(1).Cells(counter, 1)
        If curCell.Value = curCell.Offset(1, 0).Value Then
            curCell.Offset(1, 0).ClearContents
            curCell.Offset(1, 1).ClearContents
        End If
    Next counter
End Sub

I want another macro to cut and paste instead of clearing contents. With my limited vocabulary and experience, I would hope this might do the trick:

Sub CycleMovingDuplicates()
    For counter = 1 To 1000
        Set curCell = Worksheets(1).Cells(counter, 1)
        If curCell.Value = curCell.Offset(1, 0).Value Then
            curCell.Offset(1, 6).Cut
            curCell.Offset(0, 7).Paste
        End If
    Next counter
End Sub

Of course, the above code is bogus. The error code it returns is number 438: "Object doesn't support this property or method."

Does anyone know if there is a method for that object that would work for me? Or, maybe a more appropriate object?

I'm learning kind of haphazardly: just recording macros and splicing them into example code.

 

[Remou]

This is the Access forum, so I will suggest an Access solution: link the spread sheet table and use a query to eliminate duplicates.

You will find the Excel people in forum707.

 

[PHV]

Perhaps this ?
curCell.Offset(1, 6).Cut curCell.Offset(0, 7)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[burcello]

Thanks, PHV. That's just the bit of code I needed!

Sorry for posting in the wrong forum, but thanks for the tip, Remou. Newbie error.