Dave,

Thanks, tried it out today and have a problem....

res is showing error code 2042 when I hover above it, on line 19 "res =
Application.Match(myCell.Value, CheckRng, 0)", in debug mode.

Could it be because the item numbers I'm listing through are text ie have
numbers or dots in them!
eg. 093498120.... thirteen digits in length, 9 numeric and then 4
characters.

Dean

"Dave Peterson" wrote in message
...
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