VBA Excel Copy cells within one sheet

[james123456]

Hi All,

I have the attached worksheet.


I wanted to copy the IDs from Column A to Column L but only after comparing it with what is already in Column L and excluding anything already in L from the copy selection e.g. Column L already has ID 877104 so it should look it up in Column A and exclude it from copying over to Column L.

Tried the below code but it doesnt exactly do what it is supposed to, could you please take a look and see where im going wrong? It copies the data over to Column L, firts it skips the some of the rows in A and then leaves a blank row in L.

[VBA]
Private Sub test()

Dim w As Range
Dim e As Range

Set w = Sheets("Sheet1").Range("A" & j)
Set e = Sheets("Sheet1").Range("L" & d)


Dim d
Dim j
d = 2
j = 2
Do Until IsEmpty(Sheets("Sheet1").Range("A" & j))
If Sheets("Data").Range("L" & d) <> Sheets("Sheet1").Range("A" & j) Then
d = d + 1
e.Rows(d).Value = w.Rows(j).Value

End If
j = j + 1
Loop

End Sub
[/VBA]

Thanks in advance.

[p45cal]

try:
[VBA]Sub blah()
For Each cll In Range(Range("A2"), Range("A2").End(xlDown)).Cells
If Columns("L").Find(cll.Value, lookat:=xlWhole, LookIn:=xlFormulas) Is Nothing Then
Cells(Rows.Count, "L").End(xlUp).Offset(1).Value = cll.Value
End If
Next cll
End Sub
[/VBA]

[james123456]

Hi p45cal,

Works like a charm. Thank you so much.

Thanks

[p45cal]

define cll as a range