VBA find value in column

[Magoosball]

I am trying to write a for loop that will try to find each value in sheet 1 column A in sheet 2 column A. If the value in sheet 1 column A exists anywhere in sheet 2 column A then do nothing. If the value doesn't exist in sheet 2 column A then set sheet 1 column D to "Does not exist".

This should continue until there is no more data in sheet 1 column A.

Thanks in advance!

 

[Scott T]

try

Sub findon2()

Dim lr As Long
Dim lr2 As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet


Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row


'clear column D of any old "Does not exist"
ws1.Range("D2:D" & lr).ClearContents




For x = 2 To lr
    If Application.CountIf(ws2.Range("A2:A" & lr2), ws1.Cells(x, "A")) = 0 Then ws1.Cells(x, "D") = "Does not exist"
Next x




End Sub

 

[footoo]

Why not just use a formula in Sheet1 column D ?

If a macro is really required, don't need a loop. Can enter formula and convert to values, or enter values directly with the Evaluate method.

 

[Magoosball]

Just tested this. It worked! Thank you!!

 

[footoo]

Sub v()
Dim lr1&, lr2&
lr1 = Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Range("D1:D" & lr1) = Evaluate("if(countif(Sheet2!A1:A" & lr2 & ",A1:A" & lr1 & ")=0,""Does not exist"","""")")
End Sub

 

[Magoosball]

Sub v()
Dim lr1&, lr2&
lr1 = Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Range("D1:D" & lr1) = Evaluate("if(countif(Sheet2!A1:A" & lr2 & ",A1:A" & lr1 & ")=0,""Does not exist"","""")")
End Sub

Overall I was looking for VBA because this is part of a much larger script.
This worked really nicely as well. Thank you both!