Search/Find function integrated into worksheet?

I want to put a box at the top of my worksheet where I can enter a part number, and find this cell within the spreadsheet; like a permanent ctrl F function.

Any ideas how to get there?

Many thanks

One suggestion would be to make say A1 as the 'search box'. Then create code to detect when this has been completed and then search the worksheet.

Do a right-click on the worksheet tab name, select View Code from the pop-up menu, then copy and paste the following in . . .

Please Login or Register  to view this content.

Substitute "$A$1" for another range address if preferred.

You should also select A2 then use Windows, Freeze Panes so that the first row will always be visible even when the find feature goes elsewhere in the worksheet.

Fantastic!!!!

Thanks very much, this works perfectly, you are a star!

I've had a play around with this and it works really well, but I have hit a snag. A lot of my data includes company names, how can I get the search to work on part of the company name? At the moment it works if you type in the full company name but that is not how most people will want to search.

Any ideas?

Many thanks

Excellent, thanks very much!

Hi. I have used this within a spreasheet and it works very well. However, is there any way I can get this search feature to look within 2 columns only? I would like the search to work only in columns A & B.
Many thanks.

It seems this idea is popular, but the code needs to be adpated to various cicumstances. The original code I posted works fine if the search cell is within the search area, but errors occur with certain adaptations of the code.

I've revised the original code to cater for users' own preferences, as shown below....

Please Login or Register  to view this content.

As before, right-click on the tab name, select View Code to get to the visual basic window then copy and paste in the above code.

Alter the SearchCell and SearchRng (highlighted in red above) where indicated, as required.

I've also now incorporated a new feature! If the search text has an asterisk * at the end it will search for close matches, otherwise it searches for exact matches.

Enjoy!

this is awesome stumbled upon it looking for help with a macro to do almost this, any chance you can add a find next function the book im using it in has the same companys listed mor than once it works great tho especially liked the * find closest match part of it just really nead to be able to toggle to next if theres more than one of the same entry

Dear Loz

This is very usefull to me.
I would like to make the "Find box" en one sheet but searching in an other, but in the same workbook. Can you help me with the coding?

Many thanks in advance

Millhill

Originally Posted by sjm

I want to put a box at the top of my worksheet where I can enter a part number, and find this cell within the spreadsheet; like a permanent ctrl F function.

Any ideas how to get there?

Many thanks

Originally Posted by Loz

One suggestion would be to make say A1 as the 'search box'. Then create code to detect when this has been completed and then search the worksheet.

Do a right-click on the worksheet tab name, select View Code from the pop-up menu, then copy and paste the following in . . .

Please Login or Register  to view this content.

Substitute "$A$1" for another range address if preferred.

You should also select A2 then use Windows, Freeze Panes so that the first row will always be visible even when the find feature goes elsewhere in the worksheet.

This worked perfectly as well!

Is it possible:
- To revise the code to search and find the string of characters entered in cell A1 instead of searching for an exact match?
- To revise the code to do a "find next" after the first occurrence is found?