Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching array for text from array
Hey all,
Here is what I'm trying to write a macro to do. I have a column, A, of text. I want to to search/find where this text exists in a separate range. I am having trouble getting the macro to shift the active cell and then search for that term. So, I first want to search range B1:B6 for "dog" and get a report of it's location. Then, I want the macro to shift my active cell to A2 and search B1:B6 for "cat". There will be cases where the search term (ie "cat") doesn't exist. however, all I get is: Columns("A:A").EntireColumn.Select Selection.FindNext(After:=ActiveCell).Activate and this doesn't give me the option to move the active cell (the search criteria). A B 1 dog cat 2 cat horse 3 horse elephant 4 sheep cow 5 cow cat 6 pig dog Or, a simple IF/search formula would work, but I can't get that to work either. For example, I can't get a correct answer for Search(A1, B$1:B$100), even though I know the values exist. thanks, Jordan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching array for text from array
i'm really not sure what you want, but maybe this will help
Dim rngfound As Range, rngaddress As String Dim arr As Variant Dim ws As Worksheet Dim z As Long, i As Long Sub find_it() Set ws = Worksheets("sheet1") arr = Range("a1:a2").Value Set rngfound = ws.Range("B1:B6").Find(What:=ws.Range("a1").Value, _ LookIn:=xlValues) Debug.Print rngfound.Address If Not rngfound Is Nothing Then rngaddress = Range(rngfound.Address).Offset(0, -1).Address Set rngfound = ws.Range("A1:a6").Find(What:=Range(rngaddress).Val ue, _ LookIn:=xlValues) Debug.Print rngfound.Address End If End Sub -- Gary "skyboy_psu" wrote in message ups.com... Hey all, Here is what I'm trying to write a macro to do. I have a column, A, of text. I want to to search/find where this text exists in a separate range. I am having trouble getting the macro to shift the active cell and then search for that term. So, I first want to search range B1:B6 for "dog" and get a report of it's location. Then, I want the macro to shift my active cell to A2 and search B1:B6 for "cat". There will be cases where the search term (ie "cat") doesn't exist. however, all I get is: Columns("A:A").EntireColumn.Select Selection.FindNext(After:=ActiveCell).Activate and this doesn't give me the option to move the active cell (the search criteria). A B 1 dog cat 2 cat horse 3 horse elephant 4 sheep cow 5 cow cat 6 pig dog Or, a simple IF/search formula would work, but I can't get that to work either. For example, I can't get a correct answer for Search(A1, B$1:B$100), even though I know the values exist. thanks, Jordan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching array for text from array
Here is a different interpretation:
Sub SearchFor() Dim rng1 as Range, rng2 as Range Dim rng3 as Range, cell as Range Dim res as Variant set rng1 = Range(cells(1,1),Cells(1,1).End(xldown)) set rng2 = Range(cells(1,2),Cells(1,2).End(xldown)) for each cell in rng1 cell.Select res = Application.Match(cell,rng2,0) if not iserror(res) then set rng3 = rng2(res) msgbox cell & " value found at " & rng3.Address else msgbox cell & " value not found" end if Next End Sub -- Regards, Tom Ogilvy "skyboy_psu" wrote in message ups.com... Hey all, Here is what I'm trying to write a macro to do. I have a column, A, of text. I want to to search/find where this text exists in a separate range. I am having trouble getting the macro to shift the active cell and then search for that term. So, I first want to search range B1:B6 for "dog" and get a report of it's location. Then, I want the macro to shift my active cell to A2 and search B1:B6 for "cat". There will be cases where the search term (ie "cat") doesn't exist. however, all I get is: Columns("A:A").EntireColumn.Select Selection.FindNext(After:=ActiveCell).Activate and this doesn't give me the option to move the active cell (the search criteria). A B 1 dog cat 2 cat horse 3 horse elephant 4 sheep cow 5 cow cat 6 pig dog Or, a simple IF/search formula would work, but I can't get that to work either. For example, I can't get a correct answer for Search(A1, B$1:B$100), even though I know the values exist. thanks, Jordan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching array for text from array
Tom,
That's great! My knowledge of Excel is too simplistic. I've got two followup questions for you. Could you tell me how to set this up to report the location into column C? I'm going to run a query array of 60 vs a searched array of about a thousand cells. Having a message box is good, but it will become tedious. Secondly, do you recommend a good VBA/Excel book? I'm a biochemsity with no programming knowledge, but can write basic macros. I'd like to be able to do more, but find programming books difficult to interpret with my lack of computer knowledge. Thanks, jordan Tom Ogilvy wrote: Here is a different interpretation: Sub SearchFor() Dim rng1 as Range, rng2 as Range Dim rng3 as Range, cell as Range Dim res as Variant set rng1 = Range(cells(1,1),Cells(1,1).End(xldown)) set rng2 = Range(cells(1,2),Cells(1,2).End(xldown)) for each cell in rng1 cell.Select res = Application.Match(cell,rng2,0) if not iserror(res) then set rng3 = rng2(res) msgbox cell & " value found at " & rng3.Address else msgbox cell & " value not found" end if Next End Sub -- Regards, Tom Ogilvy "skyboy_psu" wrote in message ups.com... Hey all, Here is what I'm trying to write a macro to do. I have a column, A, of text. I want to to search/find where this text exists in a separate range. I am having trouble getting the macro to shift the active cell and then search for that term. So, I first want to search range B1:B6 for "dog" and get a report of it's location. Then, I want the macro to shift my active cell to A2 and search B1:B6 for "cat". There will be cases where the search term (ie "cat") doesn't exist. however, all I get is: Columns("A:A").EntireColumn.Select Selection.FindNext(After:=ActiveCell).Activate and this doesn't give me the option to move the active cell (the search criteria). A B 1 dog cat 2 cat horse 3 horse elephant 4 sheep cow 5 cow cat 6 pig dog Or, a simple IF/search formula would work, but I can't get that to work either. For example, I can't get a correct answer for Search(A1, B$1:B$100), even though I know the values exist. thanks, Jordan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching array for text from array
Here is an example - next to the value in B, it puts the row of match in A
Sub SearchFor() Dim rng1 as Range, rng2 as Range Dim rng3 as Range, cell as Range Dim res as Variant set rng1 = Range(cells(1,1),Cells(1,1).End(xldown)) set rng2 = Range(cells(1,2),Cells(1,2).End(xldown)) for each cell in rng1 cell.Select res = Application.Match(cell,rng2,0) if not iserror(res) then set rng3 = rng2(res) rng3.offset(0,1).Value = cell.row else cell.Interior.colorIndex = 3 end if Next End Sub this one puts the row in B in the same row as the value in A Sub SearchFor() Dim rng1 as Range, rng2 as Range Dim rng3 as Range, cell as Range Dim res as Variant set rng1 = Range(cells(1,1),Cells(1,1).End(xldown)) set rng2 = Range(cells(1,2),Cells(1,2).End(xldown)) for each cell in rng1 cell.Select res = Application.Match(cell,rng2,0) if not iserror(res) then set rng3 = rng2(res) cell.offset(0,2).Value = rng3.Row else cell.Interior.colorIndex = 3 end if Next End Sub http://www.j-walk.com/ss/excel and look on the left at the link for Books. His power programming book for the appropriate version of Excel is good. -- Regards, Tom Ogilvy " wrote: Tom, That's great! My knowledge of Excel is too simplistic. I've got two followup questions for you. Could you tell me how to set this up to report the location into column C? I'm going to run a query array of 60 vs a searched array of about a thousand cells. Having a message box is good, but it will become tedious. Secondly, do you recommend a good VBA/Excel book? I'm a biochemsity with no programming knowledge, but can write basic macros. I'd like to be able to do more, but find programming books difficult to interpret with my lack of computer knowledge. Thanks, jordan Tom Ogilvy wrote: Here is a different interpretation: Sub SearchFor() Dim rng1 as Range, rng2 as Range Dim rng3 as Range, cell as Range Dim res as Variant set rng1 = Range(cells(1,1),Cells(1,1).End(xldown)) set rng2 = Range(cells(1,2),Cells(1,2).End(xldown)) for each cell in rng1 cell.Select res = Application.Match(cell,rng2,0) if not iserror(res) then set rng3 = rng2(res) msgbox cell & " value found at " & rng3.Address else msgbox cell & " value not found" end if Next End Sub -- Regards, Tom Ogilvy "skyboy_psu" wrote in message ups.com... Hey all, Here is what I'm trying to write a macro to do. I have a column, A, of text. I want to to search/find where this text exists in a separate range. I am having trouble getting the macro to shift the active cell and then search for that term. So, I first want to search range B1:B6 for "dog" and get a report of it's location. Then, I want the macro to shift my active cell to A2 and search B1:B6 for "cat". There will be cases where the search term (ie "cat") doesn't exist. however, all I get is: Columns("A:A").EntireColumn.Select Selection.FindNext(After:=ActiveCell).Activate and this doesn't give me the option to move the active cell (the search criteria). A B 1 dog cat 2 cat horse 3 horse elephant 4 sheep cow 5 cow cat 6 pig dog Or, a simple IF/search formula would work, but I can't get that to work either. For example, I can't get a correct answer for Search(A1, B$1:B$100), even though I know the values exist. thanks, Jordan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching for data in an array. | Excel Discussion (Misc queries) | |||
Searching/Counting an array | Excel Discussion (Misc queries) | |||
Searching for a value in an Array | Excel Worksheet Functions | |||
searching in an array | Excel Discussion (Misc queries) | |||
Searching an Array | Excel Programming |