The Find All Form for Excel allows you to type a search query in a text box and have the results appear in a list box. The results are narrowed down as you type. This is similar functionality to Google's auto fill or auto suggest feature when doing a Google search. See the animated screen capture below.
This is a very basic application that mimics Excel's built-in Find All feature. The code uses Chip Pearson's FindAll Function in combination with the KeyUp Event in the text box to return results in the list box as you type. A sample workbook with all the code is available for free download below.
Possible Uses
The features and capabilities can be greatly expanded to meet your needs. I've used this in an add-in that runs searches on general ledger (GL) account codes. The user can quickly search for account codes from the GL or database, and quickly insert them into the worksheet. In my application, the tables containing the account codes are stored in the add-in workbook. I've also developed additional processes to automatically update the GL tables as they are updated in the system. This makes the searches extremely fast since the underlying source data is stored in the add-in.
What could you use this tool for? Leave a comment below.
Download
The “All Sheets” file above searches all the sheets in the file. The sheet name is included in column 2 of the results along with the cell address. When an item is clicked in the results, the resulting sheet and cell are selected.
The “Copy Paste” file above pastes the clicked item in a list on a Results Sheet. This allows you to track which items the user clicks on in the results list on the form.
hi
i am in process engineering
i want to animate the chart “Time Vs Processes”
my question is ‘how to set the time range in developer code?’
Hi thank you for this VBA it has been very helpfull to me. I want to use it in another spreadsheet but i want the search box on one workbook but i need it to search items in a totally seperate spreadsheet. Please could you help me witht the VBA code
thank you
Paul
Hi Jon this macro is amazing can adapt a part of it.
I wonder how I can put more columns with other information on the results of BOX List?
thank you so much!
Hi Andre,
Great question! You will first need to change the ColumnCount property of the listbox. Here is a screenshot that shows where to find that property. It is currently set to 2 for the 2 columns in the listbox.
Then, in the FindAllMatches macro you will change the array that feeds the listbox. The screenshot below shows more details. You will just need to change the number of columns in the array, and then add additional lines to pull in the information.
If you wanted to pull in the value of the cell to the right of the found cell then you could use the following code. Notice that the column number is 3. This would be the third column in the array and listbox.
arrResults(lFound, 3) = FoundCell.Offset(0,1).Value
I hope that helps. Thanks!
Hi Jon!
This find all macro is most helpful for me. I will use it to search an inventory of refrigerators and freezers in my lab. I have also modified the code as you describe to show more columns in the Result box. However, I would also like to have headers in the Result box. I have switched to TRUE for headers in the ListBox_Results which gives me “empty” headers. Can I somehow assign cells from a worksheet to show as headers, or otherwise specifiy them? I have the same headers on all the worksheets in this workbook that I use for my inventory.
Hi Martin,
Great question! I typically create Label controls and place them above the listbox for headers. You can use the headers within the listbox, but I don’t like the look and it can get messy when programming the listbox. I hope that helps. Thanks!
Hello Jon,
When adding more columns of information to the listbox, can it be done with column references instead of using offset as you show. I would like the result in the listbox always to be pulled from column 1-5 for instance. This is since I would like to be able to search different categories of information and these are located in different columns. The offset function will not be good if my search hit is in column 3 instead of in column 1. I would like the textbox to return column 1 through 5 in both cases.
hi jon
its really good micro and I m using your micro for search my part no
its really great work, i am trying to update the information using same form
please help me
Thanks Ravi! I’m not sure I understand your question.
This search function is something I have been missing in exel for a long time, and the code you have written is just amazing! 🙂
I have tried to change some of the code, but it is getting to complex for me. So I am wondering if you can help me to adapt the code in the “Find all VBA Form – All Worksheets – Copy Paste”?
This is my problem:
In the “search sheet” I have two columns (A & B) I want to confine the search to. So I’ve made this change: “Set SearchRange = ActiveSheet.UsedRange.Columns(“A:B”).Cells”
In the search box, under “results”; I want to show the results from the corresponding cells in column A,B,C. I.e. IF my search result is from A1, A2, A …, I want to show the information from A1, A2, A …, B1,B2,B… and C1, C2, C…
In the same way if my search results is from B1, B2, B…, I want to show the information from A1, A2, A …, B1, B2, B… and C1, C2, C…
When I click the search result, I want copy and paste the information from i.e. A1, B1, C1 to a new sheet.
Is this something that is possible to solve?
Kind Regards
Eivind
Hi Eivind,
Thanks for the nice feedback. I do have a video series on 3 ways to copy and paste with VBA that might help with your code for the copy paste. I hope that helps. Thanks again!
Hi Jon
Thank you for your feedback.
I have confined the search to one column:
set SearchRange = ActiveSheet.UsedRange.Columns(“A”).Cells
and I am showing the results from the two closest columns in the listbox:
arrResults(lFound, 1) = FoundCell.Value
arrResults(lFound, 2) = Cells(FoundCell.Row, 3).Value
arrResults(lFound, 3) = Cells(FoundCell.Row, 4).Value
However, this gives me a problem in the copy paste section: “Run-time error “5”, invalid procedure call or argument
This line on debug:
strSheet = Replace(Mid(strAddress, 1, InStr(1, strAddress, “!”) – 1), “‘”, “”)
Any idea on how I can modify the code to copy paste from three columns?
Kind Regards
Eivind
Hello,
i appreciate your work,and you are amazing…!
Can i ask you a few things if you have time from the email ?
Hi Onur,
Thanks for the nice comment. Unfortunately, I am not taking on any new projects or doing consulting right now. Thanks for your understanding. 🙂
Hi Jon! Your code is perfect and works like a charm. I am trying to set up another pair of worksheets (in the same workbook) to run the macro. I have copied the code to a new module, changed the name of the Results sheet (so it is different from the Results sheet that is working with my first worksheet), and have set up a Search button on my second “data” sheet, but the results of the search on my 2nd datasheet are populating my FIRST (original) Results sheet. How do I get the revised VBA code (the one with the revised Results sheet name) to run separately from the original code that is working so well for the first pair of worksheets? Thanks SO much! You rock!
Hi Kat,
In the Copy_Paste_Result macro you can change the sRESULTS variable to another sheet name. The Copy_Paste_Result macro is in the code for the f_FindAll userform. I hope that helps.
Thanks, Jon, but as soon as I start typing in the f_FindAll2 userform I made (so I could have a separate results sheet for the separate data sheet), I get a Compile error: Sub or Function not defined, pointing to this line: Private Sub TextBox_Find_KeyUp (ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer). I can change the sRESULTS variable to another sheet name, but then ALL my results go onto that one sheet. Is there a way to have 2 different results sheets (for the Results of 2 different worksheets in the same workbook)? Thanks again!!
Hello Sir,
I am new to vba. I found your macro very helpful. Now I am trying to change it my way but failed. I want the listbox to populate from other sheet with only one column. and also search the list for only that column. But no success. Please help.
mayank
Pure Genius…..
Have been looking for a way to do this for weeks. Works like a charm.
Awesome! Thanks Beekster! 🙂
Hi,
Thank you so much. This has allowed me to have a far superior search facility than the one I built.
Awesome! Thanks Andrew! 🙂
Hi.
thanks for this!
i found that if an column or row is hidden it cant search/ find values.
is there an easy way to rewrite the code for also show hidden row/columns?
Hey Tes,
Great question! We can change the LookIn parameter to xlFormulas to look in the hidden rows and columns. This is a weird issue with the Range.Find method. When it’s set to xlValues it does not search hidden rows and columns. To change the parameter, right-click the f_FindAll form and select View Code. Then change the LookIn parameter to xlFormulas in the FindAllMatches macro. See the screenshot below for details.
I hope that helps. Let me know if you have any questions.
I am using the FindAll script in a inventory worksheet and it works great but there are a couple of problems.
Background: This workbook has several sheets one of which is a table. Another sheet has all the controls on it including the button to run the FindAll script. The FindAll form is used to search against a specific column in the table.
1) If the worksheet is hidden it returns the ranges that contain the search criteria on the form but shows an error when I attempt to go to the range. Error is: “runtime error ‘1004’ Select method of Worksheet class failed”
2) When the worksheet is not hidden it all works properly until I attempt to change a value in a cell on the sheet. It actually enters the value into the corresponding cell on the sheet that the find all button is on. In order to enter/change entries in the table after a search I have to close the form navigate back to the control sheet, click on another cell, and then go back to the table to do the work.
Any suggestions.
Thanks
Hi John,
Here are some suggestions for the issues you listed:
1) You can unhide the sheet in the code, or you can check if the sheet is hidden and return a message that tells the user the sheet is hidden. Here is some sample code to check if the sheet is visible.
2) The ShowModal property of the Userform is currently set to True. This means you cannot select cells or interact with Excel while the form is open. You can set this property to False to be able to interact with Excel while the form is open. This is called a Modeless form.
I hope that helps get you started. Thanks!
Hi your macro is great but I am looking to modify it and I am not experienced really in coding VBA at all.
I have a training database that in the first column lists procedures, second procedure number, the top row is a list of employee names, the data then goes on to state if an employee is trained, or not trained
Is there a way in which I can change the search results of this macro to something like the below.
Search criteria: Procedure #, Training Status (i.e. Trained, Not Trained, TNR)
Results would give me a list of names depending on what i enter into status criteria.
End result is to show a list of names on who is trained or not on certain procedures.
Hi
Is there an easy way to modify the code to prevent duplicates in the “Find All User Form” (Find All VBA Form – All Worksheets – Copy Paste.xls)?
My problem is that I have multiple instances of the same text and I only want to show one of them. I.e.:
xx
xx
yy
yy
Can the CountIf function be used?
Jon – I have a workbook with multiple sheets, of which 2 sheets are ‘database sheets’; meaning each of these 2 contains a structured table for data (eventually there will be 3 or 4 such ‘database sheets’). I also have other sheets that contain multiple tables that I may want to search. Subsequently I chose the “Find All VBA Form – All Worksheet.xls” version of your code to deploy.
I’m having difficulty figuring out how to change the code to loop through listobjects rather than just worksheets and utilize structured table names, e.g. Range(“TableX”) to define the strSearchAddress variable for each structured table. Any ideas how this could be done?
I adjusted your UserForm strSearchAddress code to account for using the usedrange from my 2 datasheets only, the structured table headers and total rows, and I changed the LookIn parameter to xlFormulas, and changed the “Empty” parameter to “Matters:Actions” (the names on my 2 datasheets). This works, as long as I keep the structured table in the top left corner of its respective sheet, but I know that’s a kludge and really doesn’t address using this code with structured tables. (It also seems that setting the strSearchAddress for all sheets to the maximum columns and rows used in any worksheet would result in wider searches than are necessary.)
Hi,
i am using excel vba macro to combine multiple csv file with specific data string search.But i want to copy only header from one file only.pls suggest if any way is there.
Hello Jon,
Really like this macro and it works perfectly for inventory needs. I would like to add an “update” or “edit” button where my staff can add or minus stock as they remove it from the shelf or put it back.
Thank you very much!!
Hi Andrew! I was wondering if you ever figured out how to add this functionality?
Thank you very much!
Hi,
This has been very helpful, however the amount of data I’ve is pretty large and therefore the macro stucks…. Can you add a find button may be in between so that after entering the data which I’m lokking for I’ll hit it to see the results below
Hi there,
Great Code. One quick question, how to I search only when I pressed the enter key?
I tried replacing
“If Len(f_FindAll.TextBox_Find.Value) > 1 Then”
with
“If KeyCode = vbKeyReturn Then Then”
but it did not work.
it’d be great if you could share your thoughts.
Thanks,
Ken
What a great piece of code !! I am searching a sheet with 11,500 lines and 33 columns, but the code was taking forever to find the results. I modified the minimum string length to 4 and it made all the difference in the world. I’ve also added extra columns in the results window, added some option buttons to the form and wrote a routine to put the results in the current worksheet. I’m currently trying to modify things to do a “double” search, matching 2 criteria – 1 static, 1 variable, but having a little trouble with it. Thank you for providing such a great resource !!
Hello,
Really nice code. How do I do to make a search in only one column of a worksheet?
Thanks in advance!
Hello,
It is a very helpful code. Could I know how to do to make a search in only one column?
Thanks in advance,
Luis Romero
Hello i am trying to run your macro but it keeps giving me an error compile error
Sub or Function not defined at
FoundCells = FindAllOnWorksheets(Nothing, Empty, SearchAddress:=strSearchAddress, _
FindWhat:=FindWhat, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
BeginsWith:=vbNullString, _
EndsWith:=vbNullString, _
BeginEndCompare:=vbTextCompare)
Please help me as i am new at VBA
The best option to run macro safely is export all the files from the file, that you can download. Next import these files into your file and give them a go.
Hello, if someone wants to show complete row in list box of the searched word what would be the changing that needs to be done in your code.
Amazing work!
You are truly a most valuable professional. 🙂
Hi Jon,
Firstly thank you for the knowledge that you shared.
I have downloaded and tried the ‘Find All VBA Form – All Worksheets – Copy Paste.xls’ few months ago and its working well. Lately I got an assignment that need me to seach the data and export it to another workbook. The data need to export is not all from the row, only a fews. Let’s says, the data found in active workbook searchbox is in Row 7, then when click on it (in searchbox) so the data will be exported to another workbook. The data need to export is in Cell D7, F7 & K7 only. And target workbook will filled with the exported data to B4(D7), G8(F7) & L9(K7).
I knew it’s can be done but I dont know how to make it. Hopely you can help me on this.
Thank you, this is great. But how to make it work for specific sheets ?
Hi,
I would advise you to go to the very bottom and read the oldest comments. Everything is explained there.
Best regards,
I have a workbook with 4 sheets, how can I apply the code?
Hi Jon,
greetings from Portugal.
In the userform “f_Findall” I want to appear a date field.
How to change, in the results, the date format “mm/dd/yyyy” to “dd/mm/yyyy”.
Many thank’s
Hey Jon,
I have hundreds of data points that I need to transfer from one excel sheet to another and those data points are not in a single row, they are in different cells.
I would like to know macros for transferring these data points easy way please.
This works great, one thing I would like is to have the code ignore hidden worksheets, otherwise if a user clicks an answer on a hidden sheet, it generates a vba error. is that possible.