Search result list within Excel (grep-like functionality)

DenisBallant

Welcome to the Mr Excel board!

Is the search down a single column? If so,
1. Select the column
2. Data|Filter|AutoFilter
3. Click the drop-down and choose (Custom)
4. Select 'Contains' and enter text of interest

Relevant rows should now be displayed.

Nope. Search is across several columns, in some cases all columns are considered.
Denis

Re: Search result list within Excel (grep-like functionality

If you have a recent enough version of XL:

CTRL+f Click the Optioins>> button to access all options. Fill out the search criteria. Select other conditions. Click the 'Find All' button.

If not, depending on your VBA comfort level, adapt the code at
Find All
http://www.tushar-mehta.com/excel/tips/findall.html

Hi there,

Could anyone provide hints on how to create a simple form that would search a sheet for a word and display the list of resulting rows - something similar to Adobe Reader 7 Search functionality ?

If I was having administrator privileges on this PC, I would simply install Cygnus and use its "grep" command on a CSV file but without that possibility, it's rather cumbersome to search an Excel sheet when you need to do it repetitively, and I need to locate a specific row each time without knowing the exact content of the cell (only searching for one or another word I know the cell contains).

Your help very much appreciated.

Denis.

Click to expand...

how to display found rows in a clickable way on screen

Dear Tusharm,

Unfortunately I have Excel 2000. Initially it wouldn't compile your FindAll but with some little change it now works. I created a user form to interface with it but I now have a problem for displaying the information about the find rows on screen.

Do you know how the resulting rows could be displayed to the user, preferably in a clickable list so we can reach the row easily from that list ?

Denis

Well, over the past 2 days, I put together a tool for an enhanced find capability. It includes features I've wanted for some time. While I am sharing the source code, the software is still copyrighted.
Enhanced Find
http://www.tushar-mehta.com/excel/software/utilities/enhanced_find/

Denis

Would something like this be any use?

Add a 'helper' column (column D in my sample). Formula in D2 (copied down): =ISNUMBER(MATCH("*"&$G$1&"*",A2:C2,0))
Then use the standard Excel AutoFilter to filter column D to TRUE.

The AutoFilter could no doubt be built into a macro that was triggered by the Workshet_Change event focussed on cell G1 in my case. Also the helper column could be hidden if you want.

Thanks a lot Tusharm and Peter.

Both solutions are very good.

Denis

Hello everyone....

I know this is a an old thread, but I'm at the end of my patience with coding VBA stuff as I don't really understand it as well as I'd like.... but I am getting better.... anyhow...

The problem I am having is that I can make the excellent enhanced-findall feature work on my document. Everything is functional, everything is good, it's working exactly the way I want it to.... but....

When I move the document into a directory on our local server, to be accessed from a hyperlink on an intranet, the enhanced find stops working, and I get a:

Runtime Error '91' Object Variable or With block variable not set.

When I use the Debug command the Runtime Error window the following line of code is highlighted:

ElseIf TypeOf SearchWhat Is Range Then

For the life of me, I can't figure out why it works on my local drive, but not on the server. If I access it directly on the server (not through the hyperlink) it doesn't work either.

I compared all the code on both, and they are exactly the same. The only thing I can think of is that something on the server (Object library??) isn't set up right.

Can someone point me in the right direction? Any help would be appreciated!!

Jester

Further to this, all I really want the Find to do is allow the user to input a string (i.e. Smith) or a portion there of (i.e. Smi), press the GO button and it would take the user to the first instance on the worksheet. Pressing GO again perhaps would take you to the next instance, etc. I like the functionality of the Enhanced-Findall because the user can still scroll, enter data, etc. The CTRL-F Find built into Excel won't allow anything until you close the pop-up.

I don't need all the other options in the Enhanced-Findall. All I need is an entry box, and a GO button. (and the coding for that of course!)