Search across multiple worksheets

Hi,

I'd like to be able to do the equivalent of a vlookup across multiple sheets (160ish) all in the same workbook.

IF POSSIBLE, id like to be able to search any keyword and return the entire row that it appears on, but im not sure if thats possible.

Cheers in advance.

Hey!

See attached.

Just imagine alot more sheets!

Anything along them rows. Most likely Short code or Code (TST1 or TST-TST-TST-1)

Looks perfect mate.

If i run into any issues, ill let you know.

Cheers

@marcosis please wait a minute. I uploaded the wrong workbook by mistake. Be back in a few minutes...

Ok, try this one. It is not case sensitive, and it will continue to cycle through the search as long as you click "No" (not "Yes" as I mistakenly said before). Also, I added a "Cancel" option to quit the search without choosing one of the results. Here's the revised code:

Please Login or Register  to view this content.

Originally Posted by marcosis

Hey,

Thanks for this.

We're really close but Ive come across a very strange issue. Certain terms that i know exist in multiple cells return only one result, and when i click no it just shows me that same selection, however many times i click no.

Any idea why that would be?

And (sorry) can we add a message box when none are found please.

Please provide a couple examples of the terms that only give one result. And, sure, we can add the Not Found notice.

- Jeff

Im not sure how much it'll help as you cant see the data im searching, as its sensitive info.

But we have a list of products that we sell and one of the popular ones has the word train in the title and theres easily 40 varieties. It finds the first and when you click no, it just shows you the first one again, no matter how often you press no.

Trouble is I don't know how to replicate the problem. I tried partial text ("TST-TST") which is in many cells in the test workbook you uploaded, but it just returned each instance in turn.

Try using Excel's built-in search and see if that finds them all. If not, then it may be some subtle difference in the data items. If it does, then it must be my code, but I have no way to find it on my side.

Perhaps if you could create a small sample workbook that contains even one of the search terms that are affected, repeated four or five times, but then substitute dummy data for all the sensitive items. It only needs to be a few records, and possibly one worksheet. Just enough for me to see where the code loses the thread.

Meanwhile, here's the revised code with the Not Found notice.

Please Login or Register  to view this content.

By any chance are the terms you can't find part of formulas? I just tried a test using a formula. If the formula result displayed the term, the search found it, but not if it didn't display.

That doesn't sound like what you're describing, but you might want to try this anyway: where the code defines the search, replace "Lookin:=xlValues" with "Lookin:=xlFormulas".

Please Login or Register  to view this content.

That will find values and formula terms.

They're just standard text values.

As a test if i do a search (ctrl + F) it finds 38 records for "train" but only cycles through the same one, through the macro.

Ive just tried a slightly longer search for train tracks, because there are many varieties called "train tracks" and it started listing them all if i clicked no.

Shouldn't the search for "train" find all of the ones listed "train tracks"

Cheers

Hmmm. Sorry about that. I'm afraid I don't know where to start looking for my error. There are many much smarter than me around here, so hopefully someone will be able to spot something.

Hi Marcosis -

I think I found the error. At some point I thought it would be a good idea to let the search keep repeating in case you didn't find the specific record you wanted the first time through. I think the problem is that the DO-LOOP never finishes on the first sheet where the term is found! It never moves on to the next sheet.

Or you can remove the REMs (') from "' Start = c.Address" and "Loop While Not c Is Nothing 'And c.Address <> Start

You can also just replace the entire macro with this code:

Please Login or Register  to view this content.

Please let me know if this resolves the issue.

- Jeff

Thats absolutely amazing!

Cheers mate.

Much, much, appreciated

You dont have to do this part for me, because i know im being a pain, but is it easy enough to get it to paste row 1 and the current row of the result.

Row 1 is always the table headers so it would help. If you tell me its pretty easy ill go away and do the research, rather than hassle you again!

Figured it out.

Cheers

Glad it worked out.