Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find in a list
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update master list with other lists | Excel Worksheet Functions | |||
How do I find the cell address of the 2nd largest of a set? | Excel Discussion (Misc queries) | |||
How do I find data from a list (or table) and insert it in a row? | Excel Discussion (Misc queries) | |||
Find a duplicate value within a list? | Excel Worksheet Functions | |||
How do I find out what items are in one list but not in another l. | Excel Discussion (Misc queries) |