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

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

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

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 wrote:

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

--

Dave Peterson

Yes,

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

Dean

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

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

--

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.

Dkso wrote:

Yes,

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

Dean

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

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

--

Dave Peterson

--

Dave Peterson

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" schreef in bericht
...
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.

Dkso wrote:

Yes,

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

Dean

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

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

--

Dave Peterson

--

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)



Jack Sons wrote:

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" schreef in bericht
...
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.

Dkso wrote:

Yes,

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

Dean

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

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

It's me again.

I hadn't converted it to my spreadsheet correctly.

Thanks Dave

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

Dkso wrote:

Yes,

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

Dean

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

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

--

Dave Peterson

--

Dave Peterson