I'm not sure I got the worksheets in the right order (or the columns), but using
application.match() is usually much quicker than looping through all the cells
multiple times.

Option Explicit
Sub Find_Store2()

Dim res As Variant
Dim OrigRng As Range
Dim CheckRng As Range
Dim FoundIt As Boolean
Dim myCell As Range

With Worksheets("sheet1")
Set OrigRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Worksheets("sheet3")
Set CheckRng = .Range("c1", .Cells(.Rows.Count, "c").End(xlUp))
End With

FoundIt = False
For Each myCell In OrigRng.Cells
If IsEmpty(myCell) Then
'do nothing
Else
res = Application.Match(myCell.Value, CheckRng, 0)
If IsError(res) Then
'keep looking
Else
FoundIt = True
'and stop looking
Exit For
End If
End If
Next myCell

If FoundIt = True Then
MsgBox "found at least one match"
Else
MsgBox "found no matches"
End If
End Sub


Dkso wrote:

Hi,

I have a list of item codes on sheet3 that I wish to compare against another
list on sheet1, and if any of the items in the list on sheet3 match those on
sheet one do something.

I have the following code but think it's very slow and can be improved on,
can anyone help me please?
So far my code only works on one cell on sheet three, it needs to read a
list, one at a time.

Private Sub Find_Store()
Row = 1
found = False

check = IsEmpty(Range("Sheet1!A" & Row))
While (check = False)
a = Range("Sheet1!A" & Row).Text
If a = Cells.Range("c1").Text Then
found = True
foundrow = Row
End If
Row = Row + 1
check = IsEmpty(Range("Sheet1!A" & Row))
Wend
If found = True Then
find_row = foundrow

Else
find_row = -1
End If

End Sub

Please help.

Dean
--

http://www.dkso.co.uk/
http://homepage.ntlworld.com/dkso

--

Dave Peterson