find text from one column in another

[jorda...@gmail.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

thanks for your help

[jorda...@gmail.com]

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.

[demec...@yahoo.com]

Assuming your sample data posted is representative,
and runs from row1 down

To compare each item in col A with those in col B

Place in C1:
=IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),"Y","N"))
Copy C1 down to the last row of data in col A

Col C will return a "Y" for items in col A found in col B, "N" if
otherwise. Nothing ie "" will be returned in col C for blank cells in
col A, or for formulas in col A, if any, returning zero length null
strings: "". Adapt the "Y", "N" returns to suit what you have in mind.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---