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.
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.