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.