Searching without activating

[Giordano Bruno]

I am trying to avoid using .Select or .Activate

Can I avoid them when searching for text in order to find the row number of the found text.

The active sheet will not be the sheet being searched.

 

[ravishankar]

Hi
try
=match(A1,Sheet2!B1:B100,0)
will search for text in A1 in col B of sheet2 and returns the row no. (to be precise, the position in the range specified, since it starts with 1, it will be same as row no.)
Ravi

 

[Giordano Bruno]

Sorry ravi,

I should have been more explicit. I was looking for a VBA solution

 

[tusharm]

It's good you are interested in avoiding selects and activates. For an example of how to use Find see
FindAll
http://www.tushar-mehta.com/excel/tips/findall.html

That page contains more than a simple example but hopefully you will be able to get the key ideas.

I am trying to avoid using .Select or .Activate

Can I avoid them when searching for text in order to find the row number of the found text.

The active sheet will not be the sheet being searched.

 

[Giordano Bruno]

Thanks tusharm, Most interesting. In my case, the matching cell will all occur in a single column in contiguous rows and I need to find only the first row and the last row. I suspect that the Findall method is rather too elaborate for that.