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