Change search code to search in many sheets

[abjac]

Hi I have below code. It works now brilliant when it search after words. It can find xlpart also so its very good and i have used it before to search in sheets with many rows.
The testsearch workbook i have attached.
In Sheet 1 there is a command button to search in the sheets.
The code search now in Sheet2. It copy the found matches which can be part to sheet "Searchresult"
I would like to have it search in sheet2 to Sheet11 all 1o sheets. copy the matches or part matches to sheet "Searchresult"
But without change too much in the code, because the searchresult are really good.
I would like also to have in sheet "Searchresult" in column C, which write which sheet its found in.
To that i can say you cant add any help column in sheets 2 to Sheets 11, if that needed it have to be in column AL, because in the real workbook, there
are many other columns.
If some can find out would be really great. The code is as below and it now as said only search in Sheet2. I have attached a testworkbook, so you
can have a look at that.

Thanks in advance
Sincerely
Abjac

Please Login or Register  to view this content.

[mehmetcik]

You may like this code but it looks very slow to me.

I will look at this for you but I will need to change your Macro.

Please Login or Register  to view this content.

[abjac]

Hi. Mehmetcik.
Its nice you will have a look, very appriciated, Yes if better code for sure. So thanks

[abjac]

Hi. Mehmetcik.
I just checked your code you made it work really fantastic. In my case i change xlwhole to xlPart because its works better. But the code is really just exactly what i needed. Thanks so much for all your help. Have a nice evening.
Sincerely
Abjac

[abjac]

Hi mehmetcik .
I have to open this thread again. Its very weird. The code i changed to xlPart which is good. And it was working. But suddenly when i type
other word not found, the message box MsgBox "Not found, Try again with another search Word", dont come up. And then i tried paste the code
in again. Suddenly the message box came up again if not found-. But this time it did not count which sheet and row. Its a very weird error. I can search again and again and it will add the found items-.But its like some happen i simply cant find out what it is. I have attached now my test work sheet again, and now here it find all but the message box if not found dont pop up.
Sorry to open it again. This is some weird happen really, the more i run the code. Please if time have a look at the sheet i attached-
Here now it find xlPart but dont come up with message for if not found

Sincerely
Abjac

[mehmetcik]

I tried this code and it seems to work.

Please Login or Register  to view this content.

[Sintek]

Another option...

Please Login or Register  to view this content.

[abjac]

Hi mehmetcik and sintek .Thanks so much for all the work.
mehmetcik your new seems to be working with all now. Thanks so much for this really nice.

Sintek i tried your code. It seems to work just great also just some few things i will ask you about. And also fast.
Can it be changed so it look at xlPart ? and also one question if there are no data in column A, it dont find it now in column B.
Can that be changed so it will find even if forget to put a date in column A.
If time would be nice to have that and also the xlPart search
Thanks in advance
Sincerely Abjac

[Sintek]

Can it be changed so it look at xlPart

change following snippets in above code...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

what does this mean...

one question if there are no data in column A, it dont find it now in column B.
Can that be changed so it will find even if forget to put a date in column A.

[abjac]

Hi. mehmetcik and Sintek.
mehmetcik .
Your code is nearly there i just found a bug. If i search several times it overwrite i think in the first line. It shall copy the row and add it to the next free row in Sheet Searchresult. But its close try put in 2 words in several different sheets, It will find the first in all sheets. But then if you search again it overwrite the first search, but only i think first row. Try search many times it shall just come after each other in sheet Searchresult. But very close to work i think. Thanks have a look.

Sintek.
Your first code was good but only found hole matches.
The second dont really work. If i example search for fi. It will type the name fi in the searchsheet, even if it should have found the ones with name fir, and it shall not change the name, but copy the row. And also it dont include anything in column A. What i ment was it need to copy the rows. But if some example dont put a date it still need find and copy the row it there is a partial match in column B to sheet searchresult.
So yes the second not working for any partial search and some other errors also.
Hope i explained it correct hehe.

But have a look

[abjac]

Hi. mehmetcik I just checked and if i remember put date also it dont make this little but. So your solution is working brilliant now.
Sintek if not want try its ok. You have tried helped much, but the other code is working. But thanks so much for your help.
Sincerely
Abjac

[Sintek]

Just for the fun, i would like to perfect the code...

If i example search for fi

This is result for fi

Untitled.png

[mehmetcik]

Try this version.

Please Login or Register  to view this content.

[abjac]

Hi. mehmetcik. It give an error here

Sheets("Sheet" & Count).Range(MyRange).Copy sh.Range("A" & j)
Range of object worksheet failed- So not working.

Sintek.
Yours still not working. If i search for fir it should find 2 in sheet 2 one called Fir and one called Firm, it only find one.
And also when you search with a part of the word, it has to be able to find more than one even if in same sheet. It could a name very close.
And also it overwrite in the sheet Searchresult in the next search.
It should continue in the first free row, so you can make more than one search.

So sorry none of them are working. Yours Sintek i think could be interesting if it can have the last things correct.

But have a look
Sincerely
Abjac

[Sintek]

Now I understand your requirement...

Please Login or Register  to view this content.

[abjac]

Hi Sintek.
Its not working. Now its only find one item if i example search for fir . It find firm in sheet 2 not the one with fir and also it dont find firm or fir in other sheets. Same with if i search law its in 3 sheets but it only find the one in sheet 6. So now its worse actually. if i search with fir it have to find all in all sheets with have fir in the word, also if in same sheet. We have 2 in sheet 2 one in sheet 3 and one i sheet 5 . If search for law we have in 3 sheets, but it only find one.I attach the test sheet again.
Thanks for trying.But its not working have a look at the test sheet.
Sincerely abjac

I just add little to clear the understanding. If i search for a word example with 3 letters in a row(no matter big or small letters) It has to find all who have these 3 letters after each other. So when i search for fir in the sheet it has to find all who have the 3 letters. It will find the ones in the testsheet with fir and firm(Firm also have these 3 letters after each other)
But if i search for firm, i shall not find the ones with fir. It has only to find the ones have firm 4 letters after each other. Thats actually why this search is really good.
In my first macro the first post that macro do this and it add all findings after each other in sheet Searchresult.
The only problem with this macro is it only search in sheet2, and it has to be changed to search from sheet 2 to sheet 11. Important is it not overwrite in sheet searchresult. And also that it find the ones in the same sheet.
Hope its more clear.

Abjac

[Sintek]

Sorry...My bad...I posted wrong code...Amended in previous Post

Please Login or Register  to view this content.

[abjac]

Hi sintek.
Now we are talking. This code do all it has to do and work brilliant.
I know this is a challenge for you and i like that. The code is great. So thanks so much for continue try make it correct-
And i will mark it as solved now with the help from you and mehmetcik ( His solution the 2 last was good too. )
Have a nice day.
Sincerely
abjac
sorry i am allowed to give you more reputation hehe. But thanks