searching array for text from array

[skyboy_psu]

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

 

[Gary Keramidas]

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).Value, _
LookIn:=xlValues)
Debug.Print rngfound.Address
End If
End Sub

 

[Tom Ogilvy]

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

 

[summerirvin]

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

 

[Guest]

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.