Search using wildcard

**tapsmiled** · 07-29-2014, 05:13 PM

Hello,
I'm trying to perform a search of over 15,000 entries using a wildcard. Sheet1 is a list of streets in the county where I live, and I would like to be able to type a part of the name that will return every instance of that name appearing.

I found a code which i have tried to adapt, but it is not working.

Please Login or Register  to view this content.

The item being searched will be entered into TextBox1, and I'm guessing that the error I am receiving is in the .AutoFilter Field:=1, Criteria1:=TextBox1"*" line of code. This is the original sample I found onlne:

Please Login or Register  to view this content.

where they were only looking for the letters "GB"

Can anyone help?

**Paul** · 07-29-2014, 05:17 PM

Haven't tested, but did you try

Please Login or Register  to view this content.

**tapsmiled** · 07-29-2014, 06:13 PM

I have not.

**tapsmiled** · 07-29-2014, 06:19 PM

Please Login or Register  to view this content.

I modified it a bit, and what is happening is that it is taking the header row A1:N1 and transferring the header to Sheet 1. I cannot attach the file because it is not "For Public Display"

I should add that this search will likely generate multiple results. This search is designed to help people find a street when they only have a part of the name, so if the user enters "wood" into the Textbox, I would like all of the results from Column A containing "wood" to appear on sheet 2.

**tapsmiled** · 07-29-2014, 06:48 PM

I changed the Copy Destination to Range("A:A") and it populated Sheet 1 with the header in every row for thousands of rows.

**Jim885** · 07-29-2014, 06:56 PM

You were really, really close.

In addition to Paul's suggestion, change this;

Please Login or Register  to view this content.

to this;

Please Login or Register  to view this content.

**tapsmiled** · 07-29-2014, 07:22 PM

Please Login or Register  to view this content.

I tried that last suggestion, and now I'm back to the entire header row being printed on Sheet 1 and nothing going onto Sheet 2.

**tapsmiled** · 07-29-2014, 07:39 PM

okay, I've found a few issues. #1: I'm dumb. The road names are in B and not A. #2 in the Copy Destination, it is listing Sheet 2 ("A1"). First, nothing is transferring to Sheet 2; everything is going to Sheet 1. Second, does the "A1" limited the responses to only 1? Would it have to be "A:A" so that all of the responses returned by the query will go into the A column and not a single field?

Forgive me, I'm completely naive to this type of action, so I don't have a grasp on the logic.

**Jim885** · 07-29-2014, 07:56 PM

Take note of the changes below;

Please Login or Register  to view this content.

**Jim885** · 07-29-2014, 08:07 PM

Some info;

Your modified code had this;

Please Login or Register  to view this content.

Your data is in colomn B so the search has to be in column B. Not columns A1 through the last row of column B (as your code was written). That was a typo/oversight on your part.
So, this is the correction that I have in the code I provided;

Please Login or Register  to view this content.

Secondly, since you are now filtering Column B, The AutoFilter Field needed to be changed from Field:=1 to Field:=2

**tapsmiled** · 07-29-2014, 08:11 PM

Oh man, you're awesome!!! Can you alter it so that I am only seeing columns A:E when the results are populated? It is showing the entire row, and because the document has so much in it, it runs a little slow. I also added a "*" before TextBox1 so that it will search for all versions of the word.

I changed the A1:N to A1:E and it didn't limit the search to my above specifications.

**Jim885** · 07-29-2014, 08:57 PM

Change this;

Please Login or Register  to view this content.

to this;

Please Login or Register  to view this content.

**tapsmiled** · 07-29-2014, 09:02 PM

Thanks man! I actually came up with a different method that accomplished the same thing. Thank you very much!!!!! Believe it or not, this may actually, one day, help to save a life. No kidding.

**tapsmiled** · 07-29-2014, 09:13 PM

Sorry...one last question. I placed a command button on Sheet 3, where my data appears. What could I put to clear all of the cells after the search has been completed?

**Jim885** · 07-29-2014, 09:17 PM

I wasn't too keen on the code you had, and saw a few small improvements that I thought were needed. So, here's the complete version of the code I did for you.

Please Login or Register  to view this content.

You're welcome. Please mark this thread as Solved, and please leave a Rep for me! Thanks.

**Jim885** · 07-29-2014, 09:21 PM

Originally Posted by tapsmiled

Sorry...one last question. I placed a command button on Sheet 3, where my data appears. What could I put to clear all of the cells after the search has been completed?

That would be;

Please Login or Register  to view this content.

**tapsmiled** · 07-29-2014, 09:25 PM

Marked as solved and repped already!!! LOL Can you explain the differences with the new code. This other one is perfect.

**tapsmiled** · 07-29-2014, 09:26 PM

Would that clear my header too?

**Jim885** · 07-29-2014, 09:39 PM

To prevent the headers from being cleared, you will need this;

Please Login or Register  to view this content.

Whereas, Z is the last column that I guessed where your data extends to. You can change the value of Z to whatever suits the last column of your data.

The line;

Please Login or Register  to view this content.

will prevent the screen from flickering when as the code runs.
After the code completes, the last line;

Please Login or Register  to view this content.

allows the screen to refresh.

**tapsmiled** · 07-29-2014, 09:42 PM

I'm gonna try it. This is going to be such a useful tool. If you don't mind, one last question...

Is there a way to highlight the letters? If the word "fern" is searched, it would be great if "fern" had a yellow fill color just around those letters since the wildcard is before and after the entry. You have really helped me immensely. I may pick your brain on another project that is unresolved.

**Jim885** · 07-30-2014, 07:07 PM

With code, there is always a way to do something.

I ask you to start a new topic on your latest question. That way, if someone else has the same question/needs the same answer, they can look it up online, by topic.

Thanks!

**tapsmiled** · 07-30-2014, 07:18 PM

Will do! Thanks again!

Search using wildcard

LinkBack

Thread Tools

Rate This Thread

Display

Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Re: Search using wildcard

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Wildcard Search

If, And, wildcard search

Wildcard search using vlookup

Excel 2007 : search data using wildcard and sum it

Wildcard search for IF command

Bookmarks

Bookmarks

Posting Permissions