PDA

View Full Version : VBA Excel Copy cells within one sheet



james123456
02-01-2012, 09:38 AM
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.


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


Thanks in advance.

p45cal
02-01-2012, 10:23 AM
try:
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

james123456
02-01-2012, 10:26 AM
Hi p45cal,

Works like a charm. Thank you so much.

Thanks

p45cal
02-01-2012, 10:48 AM
define cll as a range