VBA Search Function Code Question

[lilsnoop]

I'd sure appreciate some help with this issue! I'm trying to accomplish the following: I've created a userform (Userform3) which has a text window (Titled: TxtMake) I would like to have whatever is typed in this text field searched throughout the entire workbook, specifically column "E" cell "8" and below. The code would have to filter that specific column (via filter button which is located in cell E7) for that specific search query term, which may or may not involve letters and/or numbers. After finding any matches from any/all of the worksheets, the matching search query (or text searched) and all related information in that same row (A8 to H8 as an example) would need to be copied and pasted in a worksheet named "AAA", which is already in existence. All the worksheets would then need to be unfiltered.

The workbook could potentially have numerous worksheets. All worksheets when created (not involving this request) will already have the filtering option on A7 through H7). As an example if I type in my search window field "Toshiba", the code will look in column E8 and down for any Matching text, & do this thru each worksheet in the workbook; It would then filter "Toshiba" then copy & paste all of the matches from each worksheet onto one summary sheet titled "AAA" and then unfilter all filtered fields in preparation for any pending any future queries.
This is what I have thus far, from code I obtained from this forum, which I altered in an effort to do this task.. I believe I've wrecked it (my apologies to the orignal author.

Please Login or Register  to view this content.

I know this is asking a lot, but if this is possible I'd sure appreciate your help!!

[StephenR]

Try this.

Please Login or Register  to view this content.

[lilsnoop]

Thank You StephenR!

I received an error msg when I tried it, but it did work partially

Please Login or Register  to view this content.

I'm attaching a sample of the database hoping it might help understand what's going on. When you open the workbook up the "Start" tab/sheet will described some of the issues I'm having.
I can't promise that my code is the greatest for the other applications in this workbook, but I've tried to piece what I learned from everyone on this site and/or through google searches. This has me stumped, though. Any help would be greatly appreciated!


Thanks again!!

edit: I've attached an updated zip sample. In the start tab I've commented on what I believe are some of the issues plaguing me. Thanks again for any/all help!

[lilsnoop]

Just curious if anyone thinks this is possible or not.. I hate to waste any more time on this project if it is pointless. Thanks for your time.

[Leith Ross]

Hello lilsnoop,

I cleaned up your code quite a bit which has sped it up and now takes less space. The code for making a worksheet has replaced by a hidden sheet which is used as a template. Another change is the code to search for the make of item has been rewritten. While Auto Filter is convenient and easy for the user, it is not a good choice here for VBA coding purposes. Your suspicion about the worksheets causing a problem for the search was correct. I have added code to ignore the sheet "template", "AAA", "Data", and "Start". Let me know what else needs to fine tuned.

Search for "Make" Macro

Please Login or Register  to view this content.

[lilsnoop]

Leith, Thanks so much for taking the time to work with me on this project!
The search functionality works great now! Reference the fine tuning...well you might of wished you hadn't mentioned that! These are some things I've just recently noticed or need still to accomplish.

1. My hope was that if a subject was in my database via a sheet name, that I could enter data by selecting them via the CboNames combobox and that any data entered from Userform2, would be entered on to that subject's worksheet. It currently puts the data on whatever sheet is open/active.

2. Regarding the same combobox "CboNames" on Userform2, if I forget to select the sheet name from the drop down, but put data in the new subject fields, i.e, name, dob, etc and then click the "Enter Personal Data" button (CommandButton5), I'll get my pop up message informing me I forgot to select the sheet for the data to be put onto. But after applying this button, all sheet names go missing from the combobox. This also affects my zip code combobox too.

3. Upon creating a new subject entry (i.e. worksheet) it no longer alphabetizes the sheets. I don't know if this will create a problem though with my Data, Start and/or AAA sheets.

4. (I'm feeling guilty asking all of this)
But would there be a way during the search query to somehow also include the name of the person from their individual sheet which would be in cell A2 from each worksheet (excluding Data, Start, & AAA sheets).


I understand if this will be to time consuming and I appreciate any/all advice or assistance you can offer!

[Leith Ross]

Hello Lilsnoop,

Questions 1 and 2 are quite easy to correct. Since there are worksheets that don't need to be alphabetized, this makes the job of alphabetizing sheets easier. As for question 4, I am not sure I understand it. It seems a lot like question 1. Can you explain what mean in more detail?

[lilsnoop]

Regarding issue #4: I'll give a work related example. If I work a burglary and the home owner had a Toshiba television stolen and if they could provide some other identifying info (Make, Model or Serial), I'd like to be able to use the search function and enter "Toshiba" and get the list that you have graciously fixed for me. The problem I have though is that I can't attribute anyone to the list of pawned Toshiba's. I'll have the date and the pawnshop, but ideally I really need the name of the person that pawned it as well and this would be in cell A2 of anyone that had a matching search query. I hope this makes sense. Again, I can't thank you enough for your time!

[Leith Ross]

Hello Lilsnoop,

I have added the worksheet sort and corrected the problems with read and writing the personal data to and from the UserForm to the worksheet. Here is the updated code which has already been added to the attached workbook. Still working on question 4.

Worksheet Sorting Macro

Please Login or Register  to view this content.

Macros to Read and Write Personal Data

Please Login or Register  to view this content.

[lilsnoop]

Leith it is looking & working great!! Thank you so much!