Excel search macro

Game - Dec 30, 2008 at 12:06 AM
 Birpal_Buttar - May 26, 2011 at 02:57 PM

I have a very tricky problem. I have got an excel sheet having some columns but more than 5000 rows. In those columns there are some names which may/may not be similar to each other. I want to make a macro which can:

1. Search for the name. e.g. If I have Katie Holmes in 1st row, Sherlock Holmes in 2nd row, Holmes Matt in 3rd row, James Holmes Dammon in 4th row and so on....then if put only Holmes in search criteria then it should get the result for all the 4 rows.

2. Secondly it should display only those 4 rows in the result. Like if we are using a sort function.

3. Thirdly I also want to have a macro so that I can reset that search criteria.

This macro is very important for me. Please reply ASAP. Thanks in advance.

PS: The search should be column specified meaning that I want the macro to search only column A and no other columns.

4 responses

buster23 Posts 15 Registration date Monday December 29, 2008 Status Member Last seen June 3, 2009 2
Dec 30, 2008 at 03:02 AM
you can learn how to do your tricks using the link:
Here's my take on this.

Don't use a Macro.

Place your list of names (or whatever) in Column D of Sheet2.

Use a dummy/sort column with a =if(iserror(Find(Sheet1!A$1,D2)),0,1) statement (column A) and another column (B) with =B1+A2 and then use column C for =A2*B2.

Set Cell A1 on Sheet1 up as your search criteria and then set Cells A2..A100 as 1, 2, 3, 4, ... for as many matches as you think you may have up to the number of possibilities.

For range B2..B100, set the cell formula as =if(iserror(vlookup(A2,Sheet2!$c$2:$D$100,2,false)),"",vlookup(A2,Sheet2!$c$2:$D$100,2,false)

The formula in Column B will find the unique identifiers (1, 2, 3, etc...) in Column C of Sheet2 and return the value in Column D. The numbers will be in sequential rows for as many as you have valid logical answers. The Find function will locate the string value indicated in cell A1 of Sheet1 anywhere it occurs in the list of string values and will generate either a 1 or 0 based on the presence of the test string. The B1+C2 formula will index the valid entries by +1 every time it recognizes them in the list and the multiplying the 2 column together you will wind up with the unique identifier set 0, 0, 0, 1, 0, 0 , 2, 0, 3, 0, ... This will probably update faster than a Macro that can do the same job.
May 26, 2011 at 02:57 PM
For all those ending up here from Google and looking for alternative to vlookup... Here is one way to get it all done with a short Macro.
The trick is to use ActiveX Textbox and run a macro to filter the table of results.

This code will Filter Data as you type and will list all possible matches appearing anywhere in text being searched

- Say your data range is A5:D1000
- First row (A5:D5) has data headers
- data you need to search is in column B

Insert an ActiveX Textbox anywhere in row 1 to 4.

Under "developer > designer mode" Double click the text box
Create new module in VBA and enter the code below. (Make sure the textbox's name matches the one defined in PRIVATE SUB)


Private Sub TextBox1_Change()

Set txt = TextBox1
Range("A5:D5").AutoFilter Field:=2, Criteria1:="=*" & txt & "*", Operator:=xlAnd

End Sub


To clear filter and return complete list, just clear the contents of textbox or create a macro button with following code


Sub Clear_Search()

Application.ScreenUpdating = False
ActiveSheet.OLEObjects("TextBox1").Object.Value = ""
Application.ScreenUpdating = True

End Sub


Further coding will depend on what you want to do further with filtered results. I use this code for invoicing system to find products from list of 2000. Once I find the product, It is added to invoice with its price.

You can add multiple textboxes to filter different columns or you can search different columns from same textbox.
Thanks for your answers.