Search Multiple Worksheets Against List of Non-Exact Search Criteria?

[thump4r]

Hi folks,

I've spent a couple hours searching for a solution to this but came up empty handed. My macro/VBA skills are non-existent, so I hope that someone can help

Here's the situation...

I have a workbook with many many sheets in it.

The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so).

If matches are found (they don't have to be exact case), then I'd like two things to happen:

1. The rows containing the matched search criteria in the first sheet are highlighted.

2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.

I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms. Hope that helps somewhat in explaining the above!

Thanks in advance.

Best,
Ian

p.s. Just realized that the title of this post is misleading.. it's not 'non-exact search criteria', but rather, 'case insensitive search criteria'.

[shg]

Welcome to the forum.

Try this:

Please Login or Register  to view this content.

[thump4r]

Hi shg,

Thank you so much for your prompt reply - I tried it against the test sheet and it worked perfectly with a slight tweak (changed the MatchCase statement to false).

I'll give it a go on the real document now.... will let you know how it turns out!

Cheers,
Ian

[thump4r]

Works great - my machine took a while to process the data, but the end result is a beautiful sight to behold. Thanks again :D

Best,
Ian

[medleylxa]

Works great - my machine took a while to process the data, but the end result is a beautiful sight to behold. Thanks again :D

Best,
Ian

Did you ever find the solution to your first question: 1. The rows containing the matched search criteria in the first sheet are highlighted.

This solution is exactly what I need and the highlighting feature would be perfect.
Thanks.