Copy to another column ?

[shumba]

Hi,

The procedure below is designed to copy a three letter code from column A to column B only if the code is not already in column B. This is not working.

Being a novice at VBA please will someone show me where I am going wrong.

[LEFT]Sub Copy_code()
Do Until IsEmpty(ActiveCell)
[LEFT] If Selection.Value = Range("b:b") Then Selection.Offset(1, 0).Select Else Selection.Copy   _    Range("b:b").Cells(Rows.Count).End(xlUp).Offset(1, 0)
[/LEFT]
Loop
[LEFT]End Sub[/LEFT]
[/LEFT]

Thanks.​

Rob.​

 

[cgcamal]

Hi shumba,

Below is a bit modified version of your code, but if the value in column A is not found in column B, it copies in B,
but one row below. I didn't modifiy this because I'm not sure if you need in that way.

Sub Copy_code()

Range("A1").Select

Do Until IsEmpty(ActiveCell)

    code = Application.WorksheetFunction.CountIf(Range("B:B"), Selection.Value)

    If code > 0 Then [COLOR=Green]'This part counts if the current value of A, is present in B[/COLOR]
        Selection.Offset(1, 0).Select
    Else
        Selection.Copy Range("b:b").Cells(Rows.Count).End(xlUp).Offset(1, 0)
    End If
Loop
End Sub

Hope this helps,

Best regards

 

[shumba]

Hi cgcamal

Thanks for your help.

Regards,

Rob.