Use Find in a loop

[Emoncada]

I have a column that i would like to see if there is a duplicate on another sheet, before it runs a macro.

So i would need it to test any values <> "" , in column "A:A" on Sheet1, and find if there is a match on Sheet2 Column "A:A"

If one matches then

If found

MsgBox("There is a Match")

else

next

I have the following code that works in a Userform but it only the value of a TextBox in the Userform, i need it to look at all values in a column now

Set Found = wd.Range("A5:A60000").Find(UserForm1.TxtSerial.Value, LookIn:=xlValues)
        
                If Found Is Nothing Then
            
                .Cells(RowNext1, 1) = UserForm2.TxtDate.Value
                .Cells(RowNext1, 2) = UserForm2.TxtSerial.Value
                .Cells(RowNext1, 3) = Environ("Username")


                Else
                
                FDate = Found.Offset(0, -2).Value
                    
                   If MsgBox("Serial Number Found on " & FDate & Chr(10) & _
                   "Do You Still Want To Process?", vbYesNo) = vbYes Then


                .Cells(RowNext1, 1) = UserForm2.TxtDate.Value
                .Cells(RowNext1, 2) = UserForm2.TxtSerial.Value
                .Cells(RowNext1, 3) = Environ("Username")



End If

[mancubus]

in the case of large data sets and assuming macro finds 10.000 matches in range A5:A60000, do you want msgbox will pop up 10.000 times?

Sub vbax_54279_Compare_Cells_In_Two_Cols_Different_WS()
     
    Dim i As Long, Found As Range
     
    For i = 2 To Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
        On Error Resume Next
        Set Found = Worksheets("Sheet2").Range("A5:A60000").Find(Worksheets("Sheet1").Range("A" & i), LookIn:=xlValues)
        On Error GoTo 0
        If Found Is Nothing Then
            'codes when no match
        Else
            'codes when match
        End If
    Next i
     
End Sub

[Emoncada]

You bring up a good point, but in most cases it will only find one or two, since the data it will look for will not exceed 3 values in Sheet1.