Option Explicit
Sub appendData()
Dim i&, lr&, lr2&, Arr, Rng, c
Dim ws1 As Worksheet, ws2 As Worksheet
Application.ScreenUpdating = False
Set ws1 = Worksheets("Data")
Set ws2 = Worksheets("Expected outcome")
lr = ws1.Cells(Rows.Count, "J").End(xlUp).Row
Arr = ws1.Range("J3:J" & lr).Value
For i = 1 To UBound(Arr, 1)
lr2 = ws2.Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = ws2.Range("B6:B" & lr2)
Set c = Rng.Find(Arr(i, 1), LookIn:=xlValues, MatchCase:=True)
If c Is Nothing Then
ws2.Cells(lr2 + 1, 2) = Arr(i, 1)
ws2.Cells(lr2 + 1, 3) = "No Data"
End If
Next i
ActiveSheet.Range("A1").Select
Application.ScreenUpdating = True
MsgBox "Finish!"
End Sub
Bookmarks