Search multiple sheets and copy results to new sheet

Give this a try:

Please Login or Register  to view this content.

Thanks so much stnkynts. I ran it but it doesn't seem to be posting results. I get a blank array name sheet. I'm using Excel 2007 if that makes a difference. Thanks again.

Shouldn't matter. It works just fine for me when I tested it. Submit your workbook.

Hi stnkynts,

Thanks for your help... very much appreciated. Your solution looks very elegant. I will send you a PM with the workbook if that's ok. Also I noticed that the search string produces results for each text in the array on new sheets individually. I was hoping the result would be for all the terms in the array. e.g. the result sheet would show all rows that contain all the text in the array ("7 days", "Monday", "Tuesday"), then I could modify the array for say ("Wednesday", "Thursday") and run the macro again to show all the rows containing all the text in the new array.

Thanks again!

Here is what you said in your original post indicating you wanted multiple sheets. Is this not the case now?

and post results in new sheets for each search string.

Yes that's correct but I meant ALL the words in the string not the individual words. Maybe I didn't clarify correctly when I said AND?

e.g.

• ROW 1 DATA: This is 7 days a week but might be closed Monday, Tuesday, Wednesday.
• SEARCH STRING: ("Monday", "Wednesday")
• RETURN: This is 7 days a week but might be closed Monday, Tuesday, Wednesday.
• SEARCH STRING: ("Monday", "Saturday")
• RETURN: No result (since the row does not contain both words "Monday" AND "Saturday")

Thanks!

Ok. Try this:

Please Login or Register  to view this content.

Due to the nature of the text in column A the macro is going to search for "mon" throughout the entire string. So for example if you have the word "Diamond" in the the string such as this:

-Diamond Rose, 10639 Kenai Spur Highway, 99611, Game play: Tue-Thu 7:00 pm and Sun 6:00 pm, Phone: (907) 283-6445.

It will return it even though it isn't on Monday. In addition, I believe the string above will also need to be counted when "Wed" is entered but it will not.

It is possible to correct for these issues but by no means easy due to the layout of the string. My time at the moment is very limited and I don't know when I will be able to address all the intricacies that you require.

Yes that's correct and it's perfectly ok if it finds the search string within words as your example "mon" within "diamond".

I'm not looking for perfection but simply results that contain ALL the search words in the array string. As in your example, search ("mon", "wed") would return lines that contain BOTH "mon" and "wed" even if the line refers to "diamond" and "wedding" OR "monday" and "wednesday" etc. Thanks!

Ok. I rewrote the code to incorporate a positive match on all strings within the Array. I also did soem reworking to speed it up a bit. Let me know:

Please Login or Register  to view this content.

Thanks stnkynts seems to be working with all the terms in the array now.

I get an error: "Object variable or With block variable not set" then I have to reopen the workbook so I can run the macro again (otherwise run button is greyed out). Also, it doesn't seem to be pulling all the results. e.g. "7 days" (I'm assuming it's not case sensitive) has 514 records but the macro stops at 197.

Thanks for the effort mate I really appreciate it.

Thanks for the case sensitive revision.

I'm manually create a new blank sheet, have my A1 cell highlighted, run the macro then as it's working, it automatically kicks over to VB and pops up the error. Attached is a screenshot.

Thanks so much!


error.jpg

If you click ok it will highlight a line of the code, what line gets highlighted. You don't need to manually create a new blank sheet. It will create 1 for you.

Interesting.

1. When I have a new blank sheet and run the macro, it throws the exception. I press ok and it goes back to the spreadsheet without highlighting anything in the VB code.
2. When I don't have a new sheet but already have results posted, no error and seems to run fine even if I revise the array and run the macro again, it overwrites fine.
3. When I don't have a new sheet or results already, and run the macro from any page, no error and posts results just fine.

I think we're good I can live with this.

Thanks so much stnkynts! You're a VBA god!