Search multiple worksheets for several keywords

You need to add code tags - see "how to" at top of page.

code tags added

What are the k1-k5 values if you're then asking user to input search item?

Do you need to have 5 values - could you not just search one at a time?

What are you doing once the values are found?

the 5 values k1-k5 are search strings (i am calling them keywords). For example the user might enter:
'dog', '534', ipod
into these cells but they may not fill up all 5 cells (i.e. so 2 are left empty), but only 1 word or number can be entered per cell that is used

The idea then is that you can narrow down your search so it will return a cell which contains all the above words and not just 1 of them.
When the values are found on any of the worksheets, i am most likely going to display the text in a msgbox.

So what is this line for?

Please Login or Register  to view this content.

It might be easier to ask the user like this to input the keywords, separated by commas, and then you don't need the k1-k5.

So you're not going to actually do anything to the cells with these values?

Could you attach a small sample workbook illustrating your data as is?

yeah, doing it via an input box is fine,
the idea is that it doesnt search for whole strings, as the various keywords could be anywhere in cell, but the condition is that the cell has to contain all keywords.

I have attached the workbook as is so far

just an update for you....
i currently now have it doing everything i want except for searching for multiple strings within the same cell. Currently it is now taking a one word or string from an Inputbox and search through all worksheets highlighting any cell with that word or string.

Can anyone help me figure out how to get this to do the same thing but to look for several strings/words anywhere with a cell?

thanks again, heres my code so far:

Please Login or Register  to view this content.

See if this works:

Please Login or Register  to view this content.

Hi Stephen,

Thanks very much for this. This certainly provides a big step to what i am trying to achieve here. Im now going to try and tweak it so it works with what im trying to do.

Thanks for the help so far

Hi Stephen
Im been playing around with the code and almost have it doing what i want. One of the problems with it originally was that if it didnt find what is was looking for in the first cell it looked in where at least 1 keyword was matched, it would goto the next sheet and skip the rest of the page.

I have added a while loop to try and get around this, so it restarts the search if not all keywords have been found, but this just then searches from the beginning of the page and not from where it last looked, so it gets stuck in an infinite loop of always looking at the same cell

Can you see a way around this?

Code included below and i have also attached my latest spreadsheet.
Thanks again

Please Login or Register  to view this content.

have this working now exactly as i need it for now. Thank you so much for your help. For reference, heres my code working as i need it at the moment:

Please Login or Register  to view this content.

Hello gyro11,

I looked for a search function and found yours. I have downloaded your attachment and code, but it doesn't seem to work. I'm getting a compile error "Variable not defined" and it highlights Set mycell = Range("a1"). Could you attach your final solution.

Best regards
Morten