EN VI

Excel - If Values from Column A in Sheet 2 doesn not exist in Column A Sheet 1 then add text to that row in sheet 1?

How to Excel - If Values from Column A in Sheet 2 doesn not exist in Column A Sheet 1 then add text to that row in sheet 1

I'm having a problem while running my vba code. I want vba to search for all the values within column A in Sheet 1 and compare it to values in column A Sheet 2. If that values doesn't exists, i want the VBA to add text to column E in Sheet 1 to that row.

These are my current code, when i run it VBA didn't give any run time error pop up and doesn't give results as well.Thank you in advance

Sub CheckValues()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim valueToFind As Variant
    Dim searchRange As Range
    Dim foundCell As Range
    
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    valueToFind = ws1.Range("A:A").Value
    
    Set searchRange = ws2.Range("A:A")
    
    ' Search for the value in the search range
    Set foundCell = searchRange.Find(what:=valueToFind, LookIn:=xlValues, lookat:=xlWhole)
    
    If foundCell Is Nothing Then
        ws1.Range("E" & foundCell.Row).Value = "Resign"
    End If
End Sub

Solution:

It is necessary to explicitly iterate through the range of data you want to find:

Note: The above code iterate through the whole column A which is time consuming. Just enough to iterate the used cells in that column.

Set valueToFind = ws1.Range("A1:A" & ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row)
    
    Set searchRange = ws2.Range("A:A")
    
    For Each valueToFindItem In valueToFind
        ' Search for the value in the search range
        Set foundCell = searchRange.Find(what:=valueToFindItem, LookIn:=xlValues, lookat:=xlWhole)
    
        If foundCell Is Nothing Then
            ws1.Range("E" & ValueToFindItem.Row).Value = "Resign"
        End If
    Next valueToFindItem