Search Engine from multiple sheets

[Flower R]

Hey, I have an excel file named inventory. In this file I have four different sheets, each containing two rows A & B. In A, I have numbers and in B text. By using VBA code or else, can I create a personalized form of “Search” with two boxes. One “input box” and other “the output”, when I should enter data in “input box”, the required result from different sheets may appear in “output box” in highlighted way (In a way it will be some sort of search engine from different sheets). Thanks.

Flower R.

[Leith Ross]

Hello Flower R,

Welcome to the Forum!

The attached workbook has a search form added to it. It will search only columns "A" and "B" of all the worksheets in the active workbook. Case is ignored in the search, but can be changed if you like. The results are put into a ListBox on the form iin the following format: Sheet1!$A$10. Again this can changed if you want something different. I choose this format because you can use this as an address directly in Formula. Here is the main search macro...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Flower R]

Thanks a lot. I shall try it. This is first time I will use VBA. Once again thanks and hoping to receive continued help.

Flower R

[Flower R]

Hello Mr. Ross,

I have copied the micro and form on my application, but it is still not working.

My workbook inventory contains three columns and sheet books as follows:

A B C

1 0123 5678 Science
2 9123 8764 fiction
3 0002 4567 Health

and so on.

when I type in search the word Science or content of column A, B, it does not show in output window. The name of the form and module are the same which you have given. I think the form code needs to be amended accordingly. Can u please help with exact coding by incorporating above data. Thanks again.

[Leith Ross]

Hello Flower R,


I changed the user form to search for all occurances of the search term on all the worksheets. The results are listed in the list box.

Sincerely,
Leith Ross

[Flower R]

Hello Mr. Ross,

I am trying to work it out still feeling some problems of its achieving. sent u a message. thanks a lot.

[Flower R]

Thanks a lot. The revised form is although working but I am having few problems in its total achieving.

• The three columns in worksheets are “Text” (Even numbers have been entered as “Text”
• The “Search Result” shows like: Sheetbooks!$C$1. I wish that it should be like; when content(value) of any Column A , B and C is entered in “Search for” text box, the result may be the value of A, B and C . An example of Sheet Books is:

A B C
1 0123 4567 Science
2 1234 4678 Science fiction
3 4321 9988 Science notes

If I enter in “Search for” box 0123 or 4567 or Science, the result may be:

0123 4567 Science

I feel that following part needs to be changed accordingly.

Please Login or Register  to view this content.

• If there is any possibility that the content of “Search result box” when clicked by mouse be highlighted and pasted in different sheet named “result”. The first result in first row, second in second and so on.
• Search function may please be restriction free i.e. without ‘match case’, or match entire cell contents. The quest for word science may give result of all three above example entries and then only the required one by clicking be pasted in “result” sheet. Similarly the quest for word fiction may fetch “science fiction” and “fiction related all words”.

I thank you once again.