Find in a list

[Dkso]

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

 

[Dave Peterson]

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]

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]

That error means that it couldn't find a match. But that following line checks
to see the results:

If IsError(res) Then

Are you sure that there was an exact match where you expected one?

 

[Dkso]

Yes,

Defiantly. I'm checking a list of 5 numbers at the moment.
I'll keep looking. Thanks

Dean

 

[Dave Peterson]

Some things to look out for:

1. Extra spaces (leading/trailing or embedded)
2. If your list is numerals, one value could be text and one could be a number.
In excel, '12345 is not the same as 12345.

(#2 shouldn't be a problem if you have alphabetic characters in those cells,
though.)

Pick out those two cells that look like they match.
use an empty cell to double check:
=a17=sheet2!a9

If it comes back true, then I'm confused. If it comes back false, look for
difficult to see differences.

Yes,

Defiantly. I'm checking a list of 5 numbers at the moment.
I'll keep looking. Thanks

Dean

 

[Jack Sons]

Dave,

What should the code be if the message in the message box should show the
number of matches found?

Jack Sons
The Netherlands

 

[Dave Peterson]

When you use application.match, you're either gonna get 1 match (the first) or 0
matches.

If you want to count how many match, you could use:

msgbox application.countif(SomeRng,SomeVal)

Dave,

What should the code be if the message in the message box should show the
number of matches found?

Jack Sons
The Netherlands

 

[Dkso]

It's me again.

I hadn't converted it to my spreadsheet correctly.

Thanks Dave

Dean