Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to search every row in sheet2 then display all matching resu
My macro button is on sheet1.
On sheet2 is an ever growing database of information. There are 5 different column values and the information is grouped by row. (not sure that explains it well) I want the macro button on sheet1 to initiate a search of the database on sheet2. I want it to search all the contents of column A (excluding A1) For every cell that matches the search I want to select that row and copy the 5 column values for that row onto sheet1. Haven't been able to figure this one out. Any help would be greatly appreciated. Thank you! Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to search every row in sheet2 then display all matching resu
Assume the item to search for in column A is in Cell B9 of Sheet1 and you
want the found rows to be pasted starting in B10 of Sheet1. Sub GetData() Dim sh As Worksheet Dim sh1 As Worksheet Dim sAddr As String, s As Variant Dim rng As Range, rng1 As Range Set sh1 = Worksheets("Sheet1") Set sh = Worksheets("Sheet2") s = sh1.Range("B9") Set rng = sh.Range(sh.Range("A2"), _ sh.Cells(Rows.Count, "A")).Find(What:=s, _ After:=sh.Cells(Rows.Count, 1), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then sAddr = rng.Address Do If rng1 Is Nothing Then Set rng1 = rng Else Set rng1 = Union(rng1, rng) End If Set rng = sh.Range(sh.Range("A2"), _ sh.Cells(Rows.Count, 1)).FindNext(rng) Loop Until rng.Address = sAddr If Not rng1 Is Nothing Then Set rng1 = Intersect(rng1.EntireRow, sh.Range("A:E")) rng1.Copy sh1.Range("B10") End If End If End Sub -- Regards, Tom Ogilvy "Ryan Hess" wrote: My macro button is on sheet1. On sheet2 is an ever growing database of information. There are 5 different column values and the information is grouped by row. (not sure that explains it well) I want the macro button on sheet1 to initiate a search of the database on sheet2. I want it to search all the contents of column A (excluding A1) For every cell that matches the search I want to select that row and copy the 5 column values for that row onto sheet1. Haven't been able to figure this one out. Any help would be greatly appreciated. Thank you! Ryan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to search every row in sheet2 then display all matching
Thank you Tom!
This worked for searching numerical values! The problem I am now having is, I need to be able to do the search for Text and Dates. When I try entering either of those I get a "Type Mismatch" error. Not sure where in the code you provided I need to modify it so that it will search properly. Thank you again! "Tom Ogilvy" wrote: Assume the item to search for in column A is in Cell B9 of Sheet1 and you want the found rows to be pasted starting in B10 of Sheet1. Sub GetData() Dim sh As Worksheet Dim sh1 As Worksheet Dim sAddr As String, s As Variant Dim rng As Range, rng1 As Range Set sh1 = Worksheets("Sheet1") Set sh = Worksheets("Sheet2") s = sh1.Range("B9") Set rng = sh.Range(sh.Range("A2"), _ sh.Cells(Rows.Count, "A")).Find(What:=s, _ After:=sh.Cells(Rows.Count, 1), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then sAddr = rng.Address Do If rng1 Is Nothing Then Set rng1 = rng Else Set rng1 = Union(rng1, rng) End If Set rng = sh.Range(sh.Range("A2"), _ sh.Cells(Rows.Count, 1)).FindNext(rng) Loop Until rng.Address = sAddr If Not rng1 Is Nothing Then Set rng1 = Intersect(rng1.EntireRow, sh.Range("A:E")) rng1.Copy sh1.Range("B10") End If End If End Sub -- Regards, Tom Ogilvy "Ryan Hess" wrote: My macro button is on sheet1. On sheet2 is an ever growing database of information. There are 5 different column values and the information is grouped by row. (not sure that explains it well) I want the macro button on sheet1 to initiate a search of the database on sheet2. I want it to search all the contents of column A (excluding A1) For every cell that matches the search I want to select that row and copy the 5 column values for that row onto sheet1. Haven't been able to figure this one out. Any help would be greatly appreciated. Thank you! Ryan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to search every row in sheet2 then display all matching
there should be no problem with a true string value such as a name. Find has
always been tempermental when it comes to dates. Dates are stored as a number. so perhaps you can convert your date to a number and have Find look for that. Also, dates can be a floating point number when they have time values added. Again, 10/25/2008 < 10/25/2008 8:00AM even if the 8:00 AM isn't displayed. So make sure you are only looking a Dates with no time (integers). You can try converting your date to a long. Play with it manually and get it to work. Then turn on the macro recorder and do the Find again to capture the settings for the arguments. Lookin:=xlformulas can also be Lookin:=xlValues with formulas, I would expect it to be looking for the number stored there (date serial number). With values, it may be looking for the displayed value as a string. So formatting would be important. Sorry I don't have a hard and fast set of rules for dates, but I have just never invested the time to exhaustively test it. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "Ryan Hess" wrote: Thank you Tom! This worked for searching numerical values! The problem I am now having is, I need to be able to do the search for Text and Dates. When I try entering either of those I get a "Type Mismatch" error. Not sure where in the code you provided I need to modify it so that it will search properly. Thank you again! "Tom Ogilvy" wrote: Assume the item to search for in column A is in Cell B9 of Sheet1 and you want the found rows to be pasted starting in B10 of Sheet1. Sub GetData() Dim sh As Worksheet Dim sh1 As Worksheet Dim sAddr As String, s As Variant Dim rng As Range, rng1 As Range Set sh1 = Worksheets("Sheet1") Set sh = Worksheets("Sheet2") s = sh1.Range("B9") Set rng = sh.Range(sh.Range("A2"), _ sh.Cells(Rows.Count, "A")).Find(What:=s, _ After:=sh.Cells(Rows.Count, 1), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then sAddr = rng.Address Do If rng1 Is Nothing Then Set rng1 = rng Else Set rng1 = Union(rng1, rng) End If Set rng = sh.Range(sh.Range("A2"), _ sh.Cells(Rows.Count, 1)).FindNext(rng) Loop Until rng.Address = sAddr If Not rng1 Is Nothing Then Set rng1 = Intersect(rng1.EntireRow, sh.Range("A:E")) rng1.Copy sh1.Range("B10") End If End If End Sub -- Regards, Tom Ogilvy "Ryan Hess" wrote: My macro button is on sheet1. On sheet2 is an ever growing database of information. There are 5 different column values and the information is grouped by row. (not sure that explains it well) I want the macro button on sheet1 to initiate a search of the database on sheet2. I want it to search all the contents of column A (excluding A1) For every cell that matches the search I want to select that row and copy the 5 column values for that row onto sheet1. Haven't been able to figure this one out. Any help would be greatly appreciated. Thank you! Ryan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to search every row in sheet2 then display all matching
Another approach that you might use it to apply an autofilter to your data
and then copy the filtered data. You might look at Ron de Bruin's site and get some ideas on how to code the autofilter (and of course the macro recorder can be useful as well) http://www.rondebruin.nl/copy5.htm -- Regards, Tom Ogilvy "Ryan Hess" wrote: Thank you Tom! This worked for searching numerical values! The problem I am now having is, I need to be able to do the search for Text and Dates. When I try entering either of those I get a "Type Mismatch" error. Not sure where in the code you provided I need to modify it so that it will search properly. Thank you again! "Tom Ogilvy" wrote: Assume the item to search for in column A is in Cell B9 of Sheet1 and you want the found rows to be pasted starting in B10 of Sheet1. Sub GetData() Dim sh As Worksheet Dim sh1 As Worksheet Dim sAddr As String, s As Variant Dim rng As Range, rng1 As Range Set sh1 = Worksheets("Sheet1") Set sh = Worksheets("Sheet2") s = sh1.Range("B9") Set rng = sh.Range(sh.Range("A2"), _ sh.Cells(Rows.Count, "A")).Find(What:=s, _ After:=sh.Cells(Rows.Count, 1), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then sAddr = rng.Address Do If rng1 Is Nothing Then Set rng1 = rng Else Set rng1 = Union(rng1, rng) End If Set rng = sh.Range(sh.Range("A2"), _ sh.Cells(Rows.Count, 1)).FindNext(rng) Loop Until rng.Address = sAddr If Not rng1 Is Nothing Then Set rng1 = Intersect(rng1.EntireRow, sh.Range("A:E")) rng1.Copy sh1.Range("B10") End If End If End Sub -- Regards, Tom Ogilvy "Ryan Hess" wrote: My macro button is on sheet1. On sheet2 is an ever growing database of information. There are 5 different column values and the information is grouped by row. (not sure that explains it well) I want the macro button on sheet1 to initiate a search of the database on sheet2. I want it to search all the contents of column A (excluding A1) For every cell that matches the search I want to select that row and copy the 5 column values for that row onto sheet1. Haven't been able to figure this one out. Any help would be greatly appreciated. Thank you! Ryan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to search every row in sheet2 then display all matching
Thank you Tom!
I was able to get the text and dates to work. You had it right from the beginning I just goofed part of the code when adjusting it for my macro. One final question I'm having trouble with. I have the following words in the database: Test, Tests, Tester... How can I adjust it so that doing a search for "Test" brings up all matches with "test" in it no matter what preceeds or follows it? Like wise with some cells containing multiple ID numbers, ie (1563, 13446, or 134) <- in one cell and doing a search for 134 and it finding said cell.? Thank you very much for all your help Tom!!!! "Tom Ogilvy" wrote: Another approach that you might use it to apply an autofilter to your data and then copy the filtered data. You might look at Ron de Bruin's site and get some ideas on how to code the autofilter (and of course the macro recorder can be useful as well) http://www.rondebruin.nl/copy5.htm -- Regards, Tom Ogilvy "Ryan Hess" wrote: Thank you Tom! This worked for searching numerical values! The problem I am now having is, I need to be able to do the search for Text and Dates. When I try entering either of those I get a "Type Mismatch" error. Not sure where in the code you provided I need to modify it so that it will search properly. Thank you again! "Tom Ogilvy" wrote: Assume the item to search for in column A is in Cell B9 of Sheet1 and you want the found rows to be pasted starting in B10 of Sheet1. Sub GetData() Dim sh As Worksheet Dim sh1 As Worksheet Dim sAddr As String, s As Variant Dim rng As Range, rng1 As Range Set sh1 = Worksheets("Sheet1") Set sh = Worksheets("Sheet2") s = sh1.Range("B9") Set rng = sh.Range(sh.Range("A2"), _ sh.Cells(Rows.Count, "A")).Find(What:=s, _ After:=sh.Cells(Rows.Count, 1), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then sAddr = rng.Address Do If rng1 Is Nothing Then Set rng1 = rng Else Set rng1 = Union(rng1, rng) End If Set rng = sh.Range(sh.Range("A2"), _ sh.Cells(Rows.Count, 1)).FindNext(rng) Loop Until rng.Address = sAddr If Not rng1 Is Nothing Then Set rng1 = Intersect(rng1.EntireRow, sh.Range("A:E")) rng1.Copy sh1.Range("B10") End If End If End Sub -- Regards, Tom Ogilvy "Ryan Hess" wrote: My macro button is on sheet1. On sheet2 is an ever growing database of information. There are 5 different column values and the information is grouped by row. (not sure that explains it well) I want the macro button on sheet1 to initiate a search of the database on sheet2. I want it to search all the contents of column A (excluding A1) For every cell that matches the search I want to select that row and copy the 5 column values for that row onto sheet1. Haven't been able to figure this one out. Any help would be greatly appreciated. Thank you! Ryan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to search every row in sheet2 then display all matching
Nevermind. I figured it out.
using:: xlPart instead of xlWhole "Ryan Hess" wrote: Thank you Tom! I was able to get the text and dates to work. You had it right from the beginning I just goofed part of the code when adjusting it for my macro. One final question I'm having trouble with. I have the following words in the database: Test, Tests, Tester... How can I adjust it so that doing a search for "Test" brings up all matches with "test" in it no matter what preceeds or follows it? Like wise with some cells containing multiple ID numbers, ie (1563, 13446, or 134) <- in one cell and doing a search for 134 and it finding said cell.? Thank you very much for all your help Tom!!!! "Tom Ogilvy" wrote: Another approach that you might use it to apply an autofilter to your data and then copy the filtered data. You might look at Ron de Bruin's site and get some ideas on how to code the autofilter (and of course the macro recorder can be useful as well) http://www.rondebruin.nl/copy5.htm -- Regards, Tom Ogilvy "Ryan Hess" wrote: Thank you Tom! This worked for searching numerical values! The problem I am now having is, I need to be able to do the search for Text and Dates. When I try entering either of those I get a "Type Mismatch" error. Not sure where in the code you provided I need to modify it so that it will search properly. Thank you again! "Tom Ogilvy" wrote: Assume the item to search for in column A is in Cell B9 of Sheet1 and you want the found rows to be pasted starting in B10 of Sheet1. Sub GetData() Dim sh As Worksheet Dim sh1 As Worksheet Dim sAddr As String, s As Variant Dim rng As Range, rng1 As Range Set sh1 = Worksheets("Sheet1") Set sh = Worksheets("Sheet2") s = sh1.Range("B9") Set rng = sh.Range(sh.Range("A2"), _ sh.Cells(Rows.Count, "A")).Find(What:=s, _ After:=sh.Cells(Rows.Count, 1), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then sAddr = rng.Address Do If rng1 Is Nothing Then Set rng1 = rng Else Set rng1 = Union(rng1, rng) End If Set rng = sh.Range(sh.Range("A2"), _ sh.Cells(Rows.Count, 1)).FindNext(rng) Loop Until rng.Address = sAddr If Not rng1 Is Nothing Then Set rng1 = Intersect(rng1.EntireRow, sh.Range("A:E")) rng1.Copy sh1.Range("B10") End If End If End Sub -- Regards, Tom Ogilvy "Ryan Hess" wrote: My macro button is on sheet1. On sheet2 is an ever growing database of information. There are 5 different column values and the information is grouped by row. (not sure that explains it well) I want the macro button on sheet1 to initiate a search of the database on sheet2. I want it to search all the contents of column A (excluding A1) For every cell that matches the search I want to select that row and copy the 5 column values for that row onto sheet1. Haven't been able to figure this one out. Any help would be greatly appreciated. Thank you! Ryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to have Sheet1 list Sheet2 rows matching text? | Excel Worksheet Functions | |||
display a value from Sheet1-A1:A10 in Sheet2-B1 | Excel Worksheet Functions | |||
macro to print sheet2 without open sheet2 | Excel Discussion (Misc queries) | |||
search Sheet2! for the contents of Sheet1! | Excel Discussion (Misc queries) | |||
Macro to search for and display data in another worksheet | Excel Worksheet Functions |