Find All VBA Form for Excel - Excel Campus
145

Find All VBA Form for Excel

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.

Find All VBA Form

Find All results in a worksheet or workbook

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

Find All VBA Form.xls (464.9 KB)

Find All VBA Form - All Worksheets.xls (1.2 MB)

File Find All VBA Form (Results on Form).xls (474.6 KB)

Find All VBA Form - All Worksheets - Copy Paste.xls (547.3 KB)

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.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 145 comments
Phil Bower - March 27, 2015

I wonder if you can help, I have downloaded your FANTASTIC Find All VBA Form spreadsheet and have imported the form and the module into my own spreadsheet, unfortunately whenever I click on a search result that appears in a worksheet other than the active one it give me an error message – Run time error ‘9’: Subscript out of range. I am new to VBA so unsure why it has an issue. Could you please help?

Reply
    Jon Acampora - March 31, 2015

    Hi Phil,

    Thanks for the comment! Is the worksheet hidden? Otherwise, you might need to activate the sheet first before selecting the cell. You should have these two lines of code the Listbox_Results_Click routine.

    Worksheets(strSheet).Select
    Worksheets(strSheet).Range(strAddress).Select

    The first line selects/activates the worksheet before selecting the cell.

    If you press the Debug button when the error comes up, what line of code does it stop at (highlighted in yellow)?

    Thanks

    Reply
      Phil Bower - July 16, 2015

      Hi Jon,

      Just wondering if it is possible to also search cell comments too?

      Kind regards,
      Phil

      Reply
        Jon Acampora - July 18, 2015

        Hi Phil,
        Yes you can search comments. In the FindAllMatches macro you will see the following code.

        Set FoundCells = FindAll(SearchRange:=SearchRange, _
        FindWhat:=FindWhat, _
        LookIn:=xlValues, _

        Change the LookIn argument to xlComments instead of xlValues. This will search the comments.

        Reply
      Phil Bower - July 17, 2015

      Hi again Jon,

      At the risk of being a pest, I am encountering a problem…

      When using the search form it correctly take me to the cell locations I am searching but I am then unable to edit cells on this sheet. Is this a worksheet activation problem?

      Kind regards,
      Phil

      Reply
        Jon Acampora - July 18, 2015

        Hi Phil,
        No worries! You’re not a pest! 🙂 I encourage questions, I just don’t always get a chance to respond quickly.

        Is the userform still open when you are trying to edit the cell? If so, you can set the ShowModal property of the form to FALSE. This will allow you to interact with the spreadsheet while the form is still open. The following screenshot shows where you can set this property of the FindAll form.

        Find All Form ShowModal Property

        This is also known as a modeless form. One drawback is that you have to make sure you close the form before navigating to other workbooks. Otherwise the form will be left open and functionality might be limited on the other workbook until you go back and close the form. This can confuse users and cause them to think Excel is frozen, even though it’s not. I hope that helps. Thanks again Phil.

        Reply
          Phil Bower - July 22, 2015

          …and thank you for the solution regarding editing cells after searching. Changing this setting has resolved my issue and has the added bonus of leaving the form open for further searches.

          You sir are a legend! Thank you 🙂

          kind regards,
          Phil

          Reply
        Phil Bower - July 22, 2015

        is it possible to search values and comments at the same time?

        Phil

        Reply
David Cornette - March 6, 2015

Hey There

Thanks for sharing this very well written article. This is almost the perfect match for my project’s need. I was just wondering if it is possible to have multiple search words in one box. (Or multiple boxes if necessary).

My search data contains +- 600 sentences describing various goals.
I would like to search these sentences for multiple words and only see the sentences that contain each of those words i type in the box but the order of the words should not matter.

If it can be done in one box with like a “&” between the words for example or multiple search boxes that would be awesome.

Thanks for advizing

Best Regards

Reply
Francisco Tuyub - March 4, 2015

Its very nice search everything…. but when I do click in the “not results found”, it takes me out of excel and a window pop-up saying error 1004.

Is there a way to click on “not results found” and make the text erase or close the form finder, or hide the error window

Reply
    Jon Acampora - March 4, 2015

    Hi Francisco,
    Great question! Find the Listbox_Results_Click sub in the f_FindAll form. You will see the following two lines of code:

    strAddress = ListBox_Results.List(l, 1)
    ActiveSheet.Range(strAddress).Select

    To ignore the error you could do the following:

    strAddress = ListBox_Results.List(l, 1)
    On Error Resume Next
    ActiveSheet.Range(strAddress).Select
    On Error Goto 0

    To erase the text try:

    strAddress = ListBox_Results.List(l, 1)
    If strAddress = "" Then
    Me.TextBox_Find.Value = ""
    Me.TextBox_Find.SetFocus
    Else
    ActiveSheet.Range(strAddress).Select
    End If

    Please let me know if you have any questions.

    Reply
gene - January 14, 2015

Jon, this routine works great. I implemented into my project and mostly it is working as expected.
However, I do find something not working here and trying to fix.(but do not know how)
The problem is: I cannot search “traditional Chinese” character. I do not know why but I know certain characters are there but I just cannot search them. English words can be found without problem.
Your suggestions would be greatly appreciated.

Reply
AJ - January 7, 2015

Hello!

I am running into the same issue as mentioned above concerning

Sub FindAllMatches().

I get an error message “Compile Error: Sub or Function not defined” and it directs me to

FoundCells = FindAllOnWorksheets(Nothing, Empty, SearchAddress:=strSearchAddress, _

I have read the previous posts and I even read the comments in the code by Chip. Can you help me resolve this?

Also, I am looking into one of your previous replies about making hidden sheets active. Where exactly would I put the command into the code? You mention that it has to be done before the FindAll runs, but where exactly? And where would the code for hiding them be put in (I assume before the “Unload Me” in the Close Command….need your help. Thanks!

Reply
Hawkins - January 2, 2015

Hello.

I have recently added two columns to my worksheet, and when using findall now I get a an overflow-error and the debugger points to ”
if. Cells(.Cells.Count). Row > MaxRow then”.

Do you have any idea why it points to this? I´m very new to VBA

Reply
Dale - November 13, 2014

Hi, please can you help? I’ve tried to use a variable for the worksheet to search however I get Type Mismatch for this line:

WSArray = Array(InWorksheets)

This is the line that uses a variable called strSelectedItem. This variable is a string that contains the sheet name.

FoundCells = FindAllOnWorksheets(Nothing, strSelectedItem, SearchAddress:=strSearchAddress, _

Hopefully that makes sense.

Thanks,

Dale

Reply
    Jon Acampora - November 20, 2014

    Hi Dale,

    Can you send me your workbook? I would be happy to take a look at it. jon@excelcampus.com

    Thank you

    Reply
      Nilanjan Roy - January 5, 2015

      Hi Jon,

      Thanks for the effort. It was very helpful for me. I have just one quick question though.

      I am getting the same Type Mismatch error that Dale spoke about on Nov 13, 2014. I got this error as I specified a particular worksheet for the FinallonWorksheet function to search on. Wanted to know if there is anyway to resolve it?

      Thanks!

      Reply
        Jon Acampora - January 5, 2015

        Hi Nilanjan,

        Checkout the documentation in the FindAllOnWorksheets function. A snippet is pasted below. I did NOT write this function, but Chip documented the code well and this should help you with the issue. Please let me know if you are still having troubles. Thanks!

        InWorksheets specifies what worksheets to search. InWorksheets can be any of the
        ‘ following:
        ‘ – Empty: This will search all worksheets of the workbook.
        ‘ – String: The name of the worksheet to search.
        ‘ – String: The names of the worksheets to search, separated by a ‘:’ character.
        ‘ – Array: A one dimensional array whose elements are any of the following:
        ‘ – Object: A worksheet object to search. This must be in the same workbook
        ‘ as InWorkbook.
        ‘ – String: The name of the worksheet to search.
        ‘ – Number: The index number of the worksheet to search.
        ‘ If any one of the specificed worksheets is not found in InWorkbook, no search is
        ‘ performed. The search takes place only after everything has been validated.

        Reply
Yawer Ali - November 12, 2014

Hi Jon,

Thank You for everything.

Reply
Yawer Ali - October 28, 2014

Hello Again,

sorry the question was about MATCH FUNCTION AS =MATCH(EmpID,Data!F:F,1) its always returns first matching value… even selected 8th one.

Reply
    Jon Acampora - October 31, 2014

    Hi Yawer,
    Yes, MATCH will always return the first value it finds. It is similar to vlookup in that regard. The FindAll function does not use MATCH. It uses Find method and loops through the search range to find each match. If you use the Find method, you can change the LookAt argument to xlWhole. This will return a match only if the search string completely matches the Found Cell. Here is more info on the Find Method.

    http://msdn.microsoft.com/en-us/library/office/ff839746(v=office.15).aspx

    Reply
Yawer Ali - October 28, 2014

Hi jon,

I am facing problem with your “Find All VBA Form for Excel” as…

if the values of different Rows are same e.g on $A$2 & $A$8 it always gives the result of top row… whether I have selected other one… which is highlighted on selection.

Naveed……..BCM0026…….January….2000
Naveed……..BCM0026…….February…3500

but always return value of January… kindly help

I am using employee code “BCM0026” for row value

Reply
Yawer Ali - October 15, 2014

Hi Jon,
Thank you for sending me auto filter for data base, I also needed this.
but my question was about “Find All VBA Form for Excel” as we can find typing any two letters in “find textbox” and have result.

I want to bind the find value with some data using combobox(class)in column no. as …

A1(sudentname) B1(Class)
jon…………..graduate
Tina………….graduate
Ali…………..underG
Peter…………graduate
Riya………….underG
Qazi………….underG

if i select graduate from combo box, the find textbox only show me the data of graduate students from column entries.

hope you got my point.

Reply
Yawer Ali - October 14, 2014

Hi Jon,

Can I filter the search on a specific word with in the columns, using combo_box values?

Reply
eddy - October 10, 2014

Thanks for your help with this!it worked fine Jon

Reply
eddy - October 1, 2014

i try to set two column, like:-
Set SearchRange = activesheet.Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
Set SearchRange = activesheet.Range(Cells(1, 8), Cells(Rows.Count, 8).End(xlUp))

but when i search records/data within column 1 it say result not found
while i search it within column 8 it showing result.

where i got wrong? thanks for responding Jon

Reply
eddy - September 30, 2014

How make f_FindAll.TextBox to find on two column (A or B) only within sheet1 instead of whole sheet. am using Find All VBA Form (Results on Form)

Reply
Yawer Ali - September 19, 2014

Hello John,

The very first I would like to say thanks for providing such a good searching forms.

Second, For, “Find All VBA Form (Results on Form)” I need the both results of

TextBox_Results1
TextBox_Results2

f_FindAll.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 5).Value
f_FindAll.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 4).Value

in specific cells of active sheet (data i will change on sheet1)as I have connected there a Rowindex no. for my results.

Kindly guide

Thanks in advance

Reply
Joe - September 13, 2014

Thanks Jon, the CountIf function is working like a champ! But yes, I am still unable to pass that variable to your form. I am not sure where in the code to declare that field as MyResponse7. Keep in mind this is not only saved in MyResponse7 but it’s also saved on the clipboard already so if it is better code to “paste” that into your form we could go that route as well.

I am wondering if a new “Upon Form Opening” module would do the trick?

Thanks for your help with this!

Joe

Reply
    Joe - September 22, 2014

    Following up on the issue of passing the contents of the clipboard, I figured it out last week and it works like a charm!

    The code behind the f_FindAll form has a subroutine right at the top of it that starts out with…

    ——————————————————-
    Private Sub TextBox_Find_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    ‘Calls the FindAllMatches routine as user types text in the textbox

    Call FindAllMatches

    End Sub
    ——————————————————–

    ^This subroutine is accessed by doing an Alt F11, click the plus in front of Forms, Right click on the f_FindAll form and choose show code.

    That code above is designed to activate upon keystrokes. My specific needs were to paste what was in the clipboard into that form. So I deleted that sub routine above from what it currently is…

    to this…

    ———————————————————
    Private Sub UserForm_Initialize()

    ‘Calls the FindAllMatches routine after it populates the find box with whatever is on the clip board

    ‘Sets the already copied clipboard contents to something that Excel can handle

    Dim DataObj As New MSForms.DataObject
    Dim S As Long
    DataObj.GetFromClipboard
    S = DataObj.GetText
    Debug.Print S

    ‘initialize clipboard paste into the TextBox_Find field.
    With Me.TextBox_Find
    TextBox_Find = S
    End With

    ‘initiates the Find All Subroutine
    Call FindAllMatches

    End Sub
    ——————————————————–

    Now it is no longer waiting for the user keystroke input and it simply pastes in the already gathered information. This saves my users only one extra keystroke, however multiplied by hundreds of times a day it becomes a valuable and less error prone time saver.

    I hope someone else will be able to make use of this altered code. That just goes to show how versatile the original Find All code is when it can be easily customized to be opened from other modules!

    Thanks once again Jon for sharing this code online!

    Joe

    Reply
Joe - September 8, 2014

Hi Jon!

As others have expressed, this is fantastic code, elegant and simple and one of the reasons why the Internet is so cool!

This coded is the solution to a few problems I’ve run into and for that and your answers to the questions above I am truly thankful.

By limiting your code to a more focused range “Column F”,
I put your find-all form into my workbook and I call it from another form which I have used for years. It works perfectly except for 2 small but nagging problems.

From the form that pulls up your form I have this code…

MyResponse7 = InputBox(“Paste ID”)

Based on the users input, it then finds the most recent data, fills my form based on that ID and then it hides my form (frmPartLoc) to call your form…

This code is what does that…
Sheets(“Data”).Select
frmPartLoc.Hide
f_FindAll.Show

First and foremost I would like to pass the user’s input “MyResponse7” to your form’s search field labeled “TextBox_Find.Text” automatically. Perhaps I am missing something but I am unable to do this so my users have to paste once in my form and then once again in your form. How do I pass that msgbox input “MyResponse7” to your form and automatically have it initiate the search?

Secondly, and less important but it would still drastically improve the process… Can we only call your form from my form ONLY IF there is more than one ID in column F?

For example, if there are 2 or more “3030”s in col “F” I would like to open and populate your form with input from “MyResponse7” or else don’t open your form since the relevant data from the one found ID in “F” has already populated my form.

Can this be done with a few additions to your and my code?

I am grateful for any of your time on this and I will gladly email you my code if you would like to find out more about the tasks.

Thanks a million Jon!

Joe

Reply
    Jon Acampora - September 13, 2014

    Hi Joe,
    In regards to your question about finding duplicates in column F, the following code uses the CountIfs function to count the ID’s in column F.

    If WorksheetFunction.CountIf(rSearch, MyResponse7) > 1 Then
    ‘code to open find all form
    End If

    Did you figure out the issue with passing variables between forms?

    Reply
      Joe - September 22, 2014

      Hi Jon,

      I addressed this in the thread just below this one. See below for answer.

      Reply
Paul - August 29, 2014

Just wanted to say thanks for this! Very close to what I was looking to do, and you saved me all the work. Much appreciated!

Reply
Ken - August 21, 2014

Hi Jon,

Thanks a lot for this.

I have modified your code so that it will display multiple columns,

arrResults(lFound, 1) = Cells(FoundCell.Row, 1).Value
arrResults(lFound, 2) = Cells(FoundCell.Row, 2).Value
arrResults(lFound, 3) = Cells(FoundCell.Row, 3).Value
arrResults(lFound, 4) = Cells(FoundCell.Row, 4).Value
arrResults(lFound, 5) = FoundCell.Address

and I wanted to keep find button on a separate worksheet so when I search, my results will get displayed on the result list.

However, the list results on the form only will only display when the database worksheet is select. When I select my other worksheet, the list results is blank, except arrResults(lFound, 5) = FoundCell.Address

Note that I changed the ShowModal property to False.

Is it because the indirect reference of the cell will not show when the worksheet is not activate?

Thank you,
Ken

Reply
    Jon Acampora - August 25, 2014

    Hi Ken,

    Assuming you are using the “Find All VBA Form.xls” version, line 46 of the f_FindAll module should look like the following.

    Set SearchRange = ActiveSheet.UsedRange.Cells

    This is setting the search range to the used range on the active sheet. So if the form is not modal, the active sheet will be searched. To change this behavior you can change that line of code to something like the following.

    Set SearchRange = Worksheets("database worksheet").UsedRange.Cells

    Change “database worksheet” to the name of the sheet that contains the data you want to search.

    You can also change the UsedRange property to reference a specific range or Table.

    Please let me know if that answers your question. Thanks!

    Reply
Adrian - August 18, 2014

Hi Jon,

First of all, your website is great and the code you produced is extremely useful. thanks a lot!

However, I have encounter a bit of problem and couldn’t resolve. As each cell contains around 10 words, I have used ALT + Enter to create 3-4 lines to make it easier to read. However, when I run the macro, the results shows a “¶” symbol when it encounter the new line. Is there anyway to hide/remove that symbol?

Thanks
Adrian

Reply
    Jon Acampora - August 20, 2014

    Hi Adrian,

    Yes you can replace the paragraph character with a blank space using the replace function.

    On line 67 of the f_FindAll form you should see the following line of code.

    arrResults(lFound, 1) = FoundCell.Value

    Replace that line of code with the following.

    arrResults(lFound, 1) = Replace(FoundCell.Value, vbLf, " ")

    This will replace the new line character (vbLF) with a blank space. Please let me know if you have any other questions.

    Thanks

    Reply
Tom - July 20, 2014

Hi Jon,

I have found this Findall incredibly useful for my stocklist at work.

I have made the changes you suggested on January 13th to offset the result by one, which has been useful but my question is similar to the question asked on January 12th, is it possible to modify the code to show the two columns next to searched term?

Thanks
Tom

Reply
    Jon Acampora - August 20, 2014

    Hi Tom,

    Sorry to not get back to you sooner! If you want to show the two columns to the right of the search term then you could modify those lines of code starting in line 68 to be the following.

    arrResults(lFound, 1) = FoundCell.Offset(, 1).Value
    arrResults(lFound, 2) = FoundCell.Offset(, 2).Value

    This would display the values of the cells that are offset 1 and 2 columns to the right of the found cell. The only issue is that when you click on a result, the found cell will NOT be selected because the cell address is not stored in the listbox anymore.

    A way to workaround this is to modify the code and the listbox to contain 3 columns, which includes one for the cell address. You could hide the column that contains the address if it is not needed.

    Please let me know if you have any questions. Thanks!

    Reply
Joy - July 16, 2014

Hi,

Your code is brilliant, thank you for posting it!

I wondered however if there is a way to modify it/ add to it so that it not only finds all but can then also copy and paste the row in which the value is found onto a “results sheet”?

I am new to coding and have been trying to find the above functionality everywhere but have yet to manage it.

I have a workbook with over 300 sheets in it and need to find a specific reference within the entire workbook, then copy and paste the results into a results page. I’m using Excel 2010 if that helps.

Any help would be greatly appreciated.

Reply
    Jon Acampora - July 16, 2014

    That’s a great question Joy! Yes, it is definitely possible. There are a few different ways to do it and I will share the code with you in the next day or two. I’m also going to create a video that walks you through the different options. Stay tuned. 🙂

    Reply
    Jon Acampora - August 20, 2014

    Hi Joy,

    My apologies for not getting back to you sooner. I added a file to the page named “Find All VBA Form – All Worksheets – Copy Paste.xls” that contains this functionality.

    In the f_FindAll module you will see a procedure (macro) named “Copy_Paste_Result”. This code will basically paste the data from the selected item in the results box into a Results Sheet. You can change the name of this results sheet in the code, if you change the name of the sheet in your workbook.

    In this file, the Copy_Paste_Result macro is called by the Click Event of the listbox. See the “ListBox_Results_Click” macro. This means that every time an item is clicked in the list, a result will be pasted on the results sheet.

    You could also change how the Copy_Paste_Result macro is fired. You could only do the copy/paste when an item is double clicked, or you could create a button on the form that performs the copy/paste when the button is pressed, instead of every time an item is clicked.

    There are a lot of possibilities, and I think your suggestion is a very useful enhancement to this form.

    Please let me know if you have any questions. Thanks again!

    Reply
    Jon Acampora - August 20, 2014

    You also mentioned that your workbook has over 300 sheets! Wow! You might be interested in a new add-in I just released called Tab Hound. It provides a way to quickly search your worksheets and navigate to them. It is also packed with a ton of features that will help you manage and organize the worksheets in you workbook, and perform common everyday tasks like unhiding multiple sheets at the same time. You can learn more at the following link.

    https://www.excelcampus.com/tabhound

    Reply
saman - June 5, 2014

thank you so much

Reply
Tim Josselyn - May 15, 2014

Hi Jon,

A very valuable tool, I thank you very much for the opportunity to add to my VBA skills.

I have adapted your form to suit my data. The problem I am having is that one column of data is $’s and another is dates.

What I would like to do is format the text box on the userform to show $’s in #,#### format and dates in dd/mm/yyyy format. The cells are formatted *dd/mm/yyyy, however they display in the text box as mm/dd/yyyy. MY regional setting are set for Australia of course.

Your assistance would be appreciated.

Reply
    Jon Acampora - May 15, 2014

    Hi Tim,

    That is a great question! There are a few ways you can go about that, but let me start with the basics.

    On line 67 of the f_FindAll form code you will see the following:

    arrResults(lFound, 1) = FoundCell.Value

    This line of code is adding the value of the cell that matches the search results to an array. Then the array is added to the listbox (what you referred to as text box, just to be clear).

    You could change the line of code above to something like the following to change the number format.

    arrResults(lFound, 1) = Format(FoundCell.Value, “0,000.00”)

    However, this would convert all results to the number format, including dates.

    So a lot will depend on how your data is structured, but you will need to add some conditional logic to account for the different data types. For example, if the foundcell value is a date, then format as date. If it’s a number, then format as number.

    Here is a basic IF THEN type statement that would accomplish that.

    If IsDate(FoundCell.Value) Then
    sValue = Format(FoundCell.Value, “dd/mm/yyyy”)
    ElseIf IsNumeric(FoundCell.Value) Then
    sValue = Format(FoundCell.Value, “0,000.00”)
    Else
    sValue = FoundCell.Value
    End If

    arrResults(lFound, 1) = sValue

    ‘———————————–

    sValue is a string variable that temporarily stores the formatted value.

    You could also use a SELECT statement instead of IF THEN.

    It’s best to keep in mind that the above code could really slow down the search because it is evaluating each result for the cell’s data type.

    Another approach would be to set the format based on the column that the foundcell is in. If you have a specific date column, then you could set the conditional logic to check the foundcell’s column, instead of it’s data type.

    Something like:

    If FoundCell.Column = 2 Then
    arrResults(lFound, 1) = Format(FoundCell.Value, “dd/mm/yyyy”)
    Else
    arrResults(lFound, 1) = FoundCell.Value
    End If

    As you can see, there are a lot of ways to go about this. Hopefully this will get you started. Please feel free to send me your file if you are still having troubles. jon@excelcampus.com

    Thanks

    Reply
Ted - May 5, 2014

Jon I was just browsing the page, you have provided excellent stuff. even though I don’t have a need for the code at the moment, wanted to leave a comment , that you have done great job. THANK YOU VERY MUCH. Fantastic —

Reply
Sohail - May 2, 2014

Hi Jon,

I would say it was a great time savor for me looking out to get the same functionality done. Great work. I customized it to work for a single column containing names and also made the search string length condition to be greater than 2 so that it does not get too slower on two letter searches.

Thanks a lot!

Reply
    Jon Acampora - May 7, 2014

    Awesome! Thanks Sohail! The string length greater than 2 characters is a great suggestion. It will definitely improve performance. In most circumstances you won’t need to only search for one character.

    If anyone else wants to do this you just need to modify line 44 of the F_FindAll form module:

    “If Len(f_FindAll.TextBox_Find.Value) > 1 Then”

    Change it to:

    “If Len(f_FindAll.TextBox_Find.Value) > 2 Then”

    Reply
Oscar - April 4, 2014

Hello Jon,

Great code, but a have already a userform but I would like to integrate your code, I’m a new in this but I learning, let me know if this is possible.. if you need to see my code let me know..
In advance thank you for your support.

Reply
    Jon Acampora - April 6, 2014

    Hi Oscar,

    Yes, it is definitely possible to integrate the code. First you will need to add the “modFindAll” module to your existing workbook. The easiest way to do this is drag and drop it in the VBA Editor. On the left side you should see the Project Explorer window that displays all the open workbooks. Find the “modFindaAll” module in the “Find All VBA Form.xls” file and drag and drop it to your workbook.

    Next you can copy/paste the code in the “f_FindAll” form module into your existing form’s code module. Then open the form object, where you view the design of the form, and copy/paste the TextBox_Find textbox into your form.

    You can then modify the code or form objects as needed.

    Please let me know if you have any questions.

    Reply
Patel - March 22, 2014

Hi Jon,
One of the best site, very helpful. I am trying to make search form on protected workbook. Is this some thing possible?
In build user data form is fine but cant work on hidden or protected sheet.

regards

Reply
    Jon Acampora - March 24, 2014

    Thank you for the kind words Patel! The search should work on a protected sheet, but it won’t work on a hidden sheet.

    The FindAll Function uses the Find() method and this requires the worksheet to be visible. You could add some code to make the sheet visible at run time, then hide it when the form is closed. You could use the following line of code to unhide the sheet.

    Worksheets(“worksheet name”).Visible = True

    Then set it to false before the form closes. Here is more info on the Visible property.

    http://msdn.microsoft.com/en-us/library/office/ff197786(v=office.15).aspx

    Please let me know if you have any other questions.

    Thanks

    Reply
Melissa - March 19, 2014

Hi,

This function is fantastic.I have tries copying it over to my spreadsheet, however I get a compile error message: Sub or Function not defined.It then highlights FindAll.

Is there an add-in I need to download?

Reply
    Jon Acampora - March 24, 2014

    Hi Melissa,

    Can you tell me what version of Excel you are using? I have not tested this code on the Mac version and am guessing it will take some adjustments to get it to work. Let me know if you are using a Mac, or if anyone is interested in a version for Mac and I will take a look.

    Thanks

    Reply
Balvinder Rayat - March 19, 2014

Hi Jon,

great code example you put here…it’s really helpful & solved my purpose almost 80%. Stuck here with one problem & i guess you can help me out for sure.

I’ve managed to show 8 columns under listbox using above examples/suggestions after some modification of my own. I really liked the idea of having multiple drop-downs to faster the search & wants to implement the same in my code.

All i want here is to filter the listbox results as per drop-down selections. is that possible?

any help would be highly appreciated..& sorry for my bad english.

Reply
    Jon Acampora - March 24, 2014

    Hi Balvinder,

    Yes, that is definitely possible. You will basically need to add some code to on the OnChange event of the combobox (drop-down) that will further filter the list. There are several ways to do this and it will depend on what you want to happen if the user changes the various comboboxes. If you want to send me your file I would be happy to take a look and make some recommendations. jon@excelcampus.com

    Thanks

    Reply
sbn - March 16, 2014

Hi Jon,
how to search a sheet in another workbook(different file)?

Reply
    Jon Acampora - March 20, 2014

    Hi sbn,

    Great question. The “FindAllOnWorksheets” function has an argument that allows you to specify the workbook that you want to search in. Download the “All Worksheets” version of the file above and take a look at line 81 of the code in the f_FindAll form module. It should read:

    FoundCells = FindAllOnWorksheets(Nothing, Empty, SearchAddress:=strSearchAddress, _

    The first argument in hte FindAllOnWorksheets function is the workbook name. It is currently set to “Nothing”, which means the active workbook is used. You can replace “Nothing” with a reference to a workbook. The code to reference a workbook is:

    Workbooks(“Book1”)

    Replace Book1 with the name of your workbook.

    One important thing to note is that the workbook needs to be open for this to work.

    You could use the Workbooks.Open method to automatically open the file when the Find All form is opened. Here is more info on how to open a workbook with code.

    http://msdn.microsoft.com/en-us/library/office/ff194819(v=office.15).aspx

    Please let me know if you have any questions.

    Reply
Roel - February 6, 2014

Hi Jon, I learned so much here, being a novice in VBA!
Now of course I have a question.
I would like to use the ‘FindAll’ on a database with names and adresses. When I find the keyword with the tool I would like to see the output being : name, address, phonenr, etc which is all on the line of the keyword.
Could you give me a hand with that ?
Thank you so much!

Reply
    Roel - February 6, 2014

    And, preferable the output should come on the form in a structured way 🙂
    Thanks.

    Reply
      Jon Acampora - February 7, 2014

      Hi Roel,

      I’m glad to hear your learning from this post. It’s a great discussion and development of this tool.

      I added a file that you can download above (File Find All VBA Form (Results on Form).xls). This file contains a simple example of this and can be customized to fit your needs.

      You will see the following on line 96 of the code:
      With ActiveSheet
      f_FindAll.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 5).Value
      f_FindAll.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 4).Value
      End With

      This code populates the text boxes on the form with the results of adjacent columns when a selection is made in the results box. It is currently set to return columns 4 & 5, which are the last arguments of the Cells property in the code above.

      The Cells property has the following arguments Cells(Row number, Column number). So we are saying that we want the same row of the return address (.Range(strAddress).Row), and then specify the column number. We are basically feeding it the coordinates of the cell on the sheet.

      You can add more text boxes to the form. When you click on a textbox in the form you will see it’s name in the properties window on the left side (right-click > Properties if it is not there). You can rename the textbox here, and then reference it’s name in the code when you add lines to populate the new text boxes.

      I hope this helps. Please let me know if you have any questions.

      Reply
        Roel - February 8, 2014

        Great Jon, exactly what I was looking for and I’m sure others can use it too !
        Thank you so much.
        This site is wonderfull, it will keep me learning more about Excel for a while 🙂

        Reply
Brownie - January 16, 2014

Hello Jon
Fantastic!!. Like everyone else I’ve been trying to make this work for a while. Your hidden cell address search is just what I have been after.

Thank you.

Reply
Hawkins - January 12, 2014

Hi Jon.

Can´t tell you how greatfull I am for this function. thank you, thank you! Now for my question. What I´m hoping you can help me with, is how to expand the search results to include a corresponding cell. In my example I´d like to do a search for the name of a part wich is in in colum B, but I would also like to see the part number which is located in column A in the results. This way I can scroll down and exclude certain part number listings that I don´t want to look at. I hope you understand what I mean and can help me.

Rgrds Hawkins

Reply
    Jon Acampora - January 13, 2014

    Hi Hawkins,

    Great question! Yes, that can be done with a few simple modifications to the code.

    Looking at the f_FindAll code module in the “Find All VBA Form.xls” file, you will need to modify the two lines of code starting in row 68 to include the Offset property. Offset allows you to offset the results by a specified number of rows or columns. Offset([Rows], [Columns]) In the example below we are setting the offset column to -1 to return the value that is one column to the left of the result value that is found in the search. The arguments for the Offset property are optional, so we can leave the Row argument blank. That explains why there is nothing before the comma in the Offset arguments: Offset(, -1)

    arrResults(lFound, 1) = FoundCell.Offset(, -1).Value
    arrResults(lFound, 2) = FoundCell.Offset(, -1).Address

    You can also modify line 46 of the code to only search in column B. This will make the search faster and limit the results to just column A because of the offset to the results we set above.

    Change line 46 to the following.

    Set SearchRange = ActiveSheet.UsedRange.Columns(2).Cells

    The Columns property allows us to specify which column of the UsedRange will be searched. In this case I set the Columns property to “2”, to search column B.

    So these two code adjustments will search column B and return the corresponding results of column A. When you click a result in the listbox on the form, the result in column A will be selected.

    Please let me know if you have any questions.

    Reply
      Hawkins - January 26, 2014

      Hi jon!

      Thank you for a great answer. I´m wondering if it´s possible to show both colums in the search field instead?? both A and the corresponding B column, in that way you can scroll down untli you find the right code in column A instead of clicking each search result. Sorry for my bad english:)

      Kind regards Hawkins

      Reply
        Jon Acampora - February 7, 2014

        Hi Hawkins,

        My apologies for not getting back to you on this sooner.

        You will just need to make a few more changes to the code. On line 64, change the following:
        ReDim arrResults(1 To FoundCells.Count, 1 To 2)
        to
        ReDim arrResults(1 To FoundCells.Count, 1 To 3)

        This is changing the array that holds the results to be 3 columns wide instead of 2.

        Then replace lines 67-68 with the following.

        arrResults(lFound, 1) = FoundCell.Offset(, -1).Value
        arrResults(lFound, 2) = FoundCell.Value
        arrResults(lFound, 3) = FoundCell.Offset(, -1).Address

        This will add the value of the cell in column B to column 2 of the array, and put the address in column 3.

        Now we need to change the listbox on the form to display the 3 columns.

        Double-click on the f_FindAll form in the Project window on the left side of the screen. You should see the Find All form displayed. Right-click on the Results Listbox, and select Properties. You should see the properties window on the left side of the screen. Change the following two properties:

        ColumnCount: 3
        ColumnWidths: 70 pt;70 pt; 70pt

        The widths can be changed based on the length of your data in columns A & B.

        That should do it. You should now be able to see the values for columns A & B in the form.

        Please let me know if you have any questions.

        Reply
Madi - December 24, 2013

hi Jon,

really perfect work. AWESOME! Thanks a lot. i have a question: is it possible to show results in a selected cell (or in a column) instead of ListBox ?

and second one; is it possible to perform a phrase search, i mean if you are looking for some text in search box might be a phrase but not exact phrase that you are looking for, for example, ceramic tableware, but written as ceramic kitchenware in search box, even it is not exact phrase what i am looking for, it shows results with the cells that including “ceramic” or “ware” words which are parts of phrase?

Thans in advance.
Madi

Reply
    Jon Acampora - December 26, 2013

    Hi Madi,

    Yes, you can show the results on the worksheet. To show the results starting in a specific cell, add the following two lines of code below line 74 of the f_FindAll code.

    Range(“L6:L” & Rows.Count).ClearContents
    Range(“L6”).Resize(UBound(arrResults), 1).Value = arrResults

    Add this code below the line that reads: Me.ListBox_Results.List = arrResults. You can delete or comment out this line if you don’t want the results to appear in the listbox.

    You can also change the “L6” reference to another cell in your worksheet. If you want the results to appear on a specific sheet that is not the active sheet, then put Worksheets(“worksheet name”) in front of the Range method.

    Worksheets(“Results”).Range(“L6:L”…

    To display the results in the selected cell you can use the following code.

    ActiveCell.Resize(Rows.Count – ActiveCell.Row).ClearContents
    ActiveCell.Resize(UBound(arrResults), 1).Value = arrResults

    However, you will want to be careful that the selected cell is not in the data range that you are searching. You can use the Intersect function to check for this and warn the user.

    In regards to your second question, it might be possible to perform a phrase search by splitting the search string and running the search multiple times. Let me know if this is something you are interested in and I can develop a customized solution for you based on your exact needs.

    Thanks!
    Jon

    Reply
      Madi - December 27, 2013

      Dear Jon,

      Thank you so much for your reply. I truly appreciate your time and kind effort.

      your answer for my 1st question is exactly what i was looking for.

      actually i have tried to perform a phrase search. i have a code that is splitting a phrase word by word.

      i have formulas (with SEARCH, RANK + COUNTIF and VLOOKUP functions) performing search text string (like keyword). when you write a keyword in a cell (for example in D3) it takes time a little bit long to retrieve data from the other sheets due to the fact that it is performed by formulas.

      i’ll send the excel file by e-mail for my project so that you can see what i am doing for searching text string.

      Thanks.

      Madi

      Reply
        Jon Acampora - December 27, 2013

        I am glad it helped you Madi.

        The split string search within the Find All Form might be slow as well, and will depend on the size of the range you are searching. I would be happy to take a look at your file. My email is jon@excelcampus.com.

        Thanks

        Reply
Steve K. - December 19, 2013

Hello Jon,

Fantastic work. I’ve been looking for this ‘tool’ for some time.
Its almost what I need, with one minor change, if possible; Is it feasible to have the results show only the sheet name without the cell showing, but still take the user to the cell (in that sheet) when they click on the result? Basically, hide the cell portion of the result(s).

Thank you so much,
Steve

Reply
    Jon Acampora - December 19, 2013

    Hi Steve,

    Yes, this is possible with a minor change to the code. You can download the updated file at the link below.

    File Find All VBA Form – All Worksheets (Hide Cell Address).xls

    I will provide some explanation on how I modified the code for anyone that is interested. This might help if you want to make changes on your own.

    The listbox that contains the results that include the worksheet name and cell address is populated by looping through the array of FoundCells that Chip’s code produces. This loop can be found starting on line 108 of the f_FindAll form. The arrResults array is populated with 3 columns: cell value, worksheet name, worksheet+cell address. In this case, the third column is NOT displayed in the listbox. The listbox has a ColumnCount = 2, so the 3rd column that contains the cell address is hidden.

    However, the 3rd column of data still exists and is loaded in the listbox. When the user clicks the listbox, the cell address of the selection is found on line 139 and the cell is selected.

    The code on line 139 is: strAddress = ListBox_Results.List(l, 2)

    This finds the value of the selected cell in column 3 of the listbox. The column index of the .List property is 2, but column 1 has a column index of zero, so you need to subtract 1 from the column number when using .List.

    Please let me know if you have any other questions.

    Thanks!

    Reply
      Steve K. - December 19, 2013

      Jon,

      Thank you very much and Happy Holidays to you and yours.

      Steve

      Reply
Ebi - December 16, 2013

Hi Jon
my name is Ebi Im Iranian
This “Find All results in a worksheet or workbook
” very useful. Thanks.
see you Later

Reply
Marshall - November 18, 2013

Tried incorporating the form and module for All Worksheets in my excel workbook but every time I type the second character in the form I get a runtime error 424 Object Required. Debugger shows it stops on this line of code in the sub FindAllMAtches:

For Each FoundCell In FoundCells(lWS)

Any idea what the problem might be?

Reply
    Jon Acampora - November 18, 2013

    Hi Marshall,

    The code was hitting an error when it did not find the search term on the sheet, when it is looping through each sheet to create the results list. I have updated the code in the “Find All VBA Form – All Worksheets.xls” file to bypass this error.

    This fix only applies to the All Worksheets file.

    Please let me know if you have any questions. Thanks for reporting this!

    Jon

    Reply
Libor - November 6, 2013

Hi Jon,
thanks for your advise and second how can I change code of the “Find All VBA Form – All Worksheets.xls” for search a specific column or range. This not same what you commented on Jun 26 for the “Find All Form.xls”. After that change I’ll can give you feedback to implement on my data.

Thanks

Reply
    Jon Acampora - November 6, 2013

    Hi Libor,
    You are correct, specifying a column or range is different with the FindAllOnWorksheets code. The third argument is the search address, which is a string instead of a range in this function. Therefore you can just replace strSearchAddress in that same line of code with your range, wrapped in double quotes.

    So line 81 of the form code currently looks like this.

    FoundCells = FindAllOnWorksheets(Nothing, “Find All:Sheet2″, SearchAddress:=strSearchAddress, _

    Change it to:

    FoundCells = FindAllOnWorksheets(Nothing, “Find All:Sheet2″, SearchAddress:=”C1:E800″, _

    You can change the range address to whatever range you are searching. To search an entire column use “C:C”, multiple columns “C:E”. If you have ranges or columns that are not continuous you can separate them with a comma “C:D,F:H”.

    Let me know if you have questions.

    Reply
Libor - November 6, 2013

Hi Jon
the form is very worthwhile and I have question, is possible in the form FindAll in Code VBA exactly specify several of sheets (Sheet1 + Sheet3 + Sheet7…) having search.

thanks

Libor

Reply
    Jon Acampora - November 6, 2013

    Hi Libor,

    Great question! Yes, this is possible. Chip’s “FindAllOnWorksheets” function allows you to specify multiple worksheets in the InWorksheets argument.

    Here are some instructions on how to do this.

    1. Download and open the “Find All VBA Form – All Worksheets.xls” file above.
    2. View the code for the “f_FindAll” form.
    3. On line 81 of the code you should see the following

    FoundCells = FindAllOnWorksheets(Nothing, Empty, SearchAddress:=strSearchAddress, _

    4. The second argument of the FindAllOnWorksheets function specifies the worksheets to search. It is currently set to “Empty” to search all the worksheets in the workbook.

    5. Change Empty to a string that includes the worksheet names you want to search separated by a colon (:).

    6. If you want to search the Find All sheet and Sheet2, then line 81 would look like the following.

    FoundCells = FindAllOnWorksheets(Nothing, “Find All:Sheet2”, SearchAddress:=strSearchAddress, _

    7. You can add additional sheets to this string, the only requirement is that they are separated by a colon. For example, “Find All:Sheet1:Sheet2” would perform the search on those three sheets.

    Please let me know if you have any questions.

    Reply
    Jon Acampora - November 6, 2013

    One way to enhance this further would be to add a multiselect listbox that contains all the sheet names in the workbook. Then you could select (with checkboxes) the sheets that you wanted to search. The selection in the listbox would feed the 2nd argument of the FindAllOnWorksheets function with a string of worksheet names separated by colons.

    I’ll try to create this solution when I get a chance. Hopefully someone will beat me to it. 🙂 If so, send it to me and I will post it here.

    Thanks!

    Reply
Pam - October 28, 2013

AWESOME!!! This is exactly what I have been searching for. I tried to get Chip Pearson’s FindAll function to work for me and stuggled (due to my inexperience), but you pulled it all together perfectly. I can’t thank you enough!

Reply
Amit - October 25, 2013

Jon,

This is very useful. Thanks.

Is there a simple way to point the search only to a particular worksheet, rather than the active sheet. Or, better still, how could I modify this to create a userform which allows me to search specific columns or named ranges?

Thanks in advance

Reply
    Jon Acampora - October 25, 2013

    Hi Amit,

    Yes, you can change the code to search a specific sheet. I explain how to search a specific column in the comments above on June 26.

    To search a different sheet you can change the the SearchRange.

    The SearchRange is currently:

    SearchRange = Activesheet.UsedRange.Cells

    Change it to:

    SearchRange = Worksheets(“Sheet1”).UsedRange.Cells

    Replace “Sheet1” with your sheet name.

    You will also want to make a change to the code for the listbox, so when the user clicks a result your “Sheet1” is selected.

    Find this code on row 91 of the userform code:

    ActiveSheet.Range(strAddress).Select

    Add a line above it:

    Worksheets(“Sheet1”).Select
    ActiveSheet.Range(strAddress).Select

    This can be used in combination with the code to search a specific named range or column. See comment on June 26.

    You can use Range(“NamedRange”) to reference a named range. Just replace “NamedRange” with your actual named range.

    Please let me know if you have any other questions.

    Thanks!

    Reply
Nick - September 13, 2013

Hi Dustin,
I’d appreciate some guidelines on implementing FinAllOnWorksheets please. Amongst other things, where in the code would I define my Array?
Thanks,
Nick

Reply
    Jon Acampora - September 18, 2013

    Hi Nick,

    I added a file in the Download section above that searches all sheets in the workbook. This should hopefully give you some guidance on how to use the FindAllOnWorksheets function.

    Let me know if you have any questions.

    Thanks,
    Jon

    Reply
Matt - August 22, 2013

Jon,
I don’t know VBA (well, enough to get me in trouble). I have a couple users who track inventory with excel. We’re talking around 9000 cells of data (item | location). Can this find all form be modified to allow the user to change the data found, in the form window? The other option could be temporarily highlight the results so the cells are easier to find. Basically have it work just like MSword or even better IE8, where it highlights all results? Then once you search something else or end the search the highlights change or are removed respectively?
Thanks
Matt

Reply
Dustin - August 21, 2013

Is there a way to have the Find All function search multiple worksheets? I have a workbook with several sheets and would like to have the data take me to the sheet and cell where it lies.

Reply
    Jon Acampora - August 22, 2013

    Hi Dustin,
    Good question. Yes, Chip’s code contains a function named FindAllOnWorksheets that will do this. The Find All Form could be modified to utilize this function to return results from multiple sheets. Let me know if you have any questions on how to implement it.

    Thanks,
    Jon

    Reply
Rachael - July 31, 2013

I should first explain that I am not code savvy. The fact that I am copy and pasting your button into my document is obviously not correct.

I love the find all button you have created so I copied and pasted it into my document. It works great but when I use it, your demo document pops up too.

How do I unlink your excel demo from the find all button code?

Reply
    Jon Acampora - July 31, 2013

    Hi Rachael,

    Great question! This should be easy to implement in your own workbook, and you don’t need to know any coding. You basically need to copy the form and code modules into your workbook along with the button, then assign a macro to the button in your workbook. The video on the following page demonstrates the same principle for the zoom on charts macro.

    https://www.excelcampus.com/vba/zoom-on-excel-charts/

    You might want to watch that video for a visual demonstration. It will be a similar process for the Find All form and the following instructions will guide you through it.

    1. Open the FindAll form workbook and your workbook.
    2. From any Excel window, open the VBA Editor by pressing Alt+F11 on the keyboard.
    3. The VBA Editor window should contain a list of projects on the left column of the screen. These are all the workbooks you have open. (If you don’t see the Project Window on the left side, press Ctrl+R to enable it.)
    4. Locate the “Find All VBA Form.xls” file in the list and click the plus (+) icon next to it to expand it. Click the plus icons next to the “Forms” and “Modules” folders. You should see the “f_FindAll” form and “modFindAll” module. See the attached screenshot for details.
    5. Left-click on the “F_FindAll” name, hold down the mouse button and drag it to your workbook. Once your workbook name turns blue, release the mouse button to drop it in the workbook.
    6. Repeat step 5 for the “modFindAll” module – drag and drop it into your workbook.
    7. Your workbook now contains the Find All form and code.
    8. If you are using Excel 2007 or later, you might need to re-save your workbook as a Macro-enabled workbook. If the file extension of your workbook is currently “.xlsx”, you will need to do a File>Save As and choose “Excel Macro-Enabled Workbook” from the Save as type drop-down menu below the file name box.
    9. Copy the button from the Find All Form workbook and paste it into your workbook.
    10. Right-click on the button in your workbook and choose “Assign macro…”
    11. Locate the “Macros in:” drop-down box and choose “This Workbook” from the list.
    12. Select “Show_FindAll_Form” from the list above and click OK.
    13. You should now be able to click on the button in your workbook to launch the Find All form.

    It is important to note that the Find All form will search on the entire used range of the active worksheet. You do not need to set this range, but if there is a large amount of data on your sheet then the form might be slow to respond.

    Please let me know if this helps and if you have any additional questions.

    Thanks,

    Jon Acampora

    Reply
      H - November 27, 2013

      Hi Jon,

      Thank you so much for this. A great search feature and your explanation on how to prevent the demo pop up was absolutely spot on. You rock!

      Reply
Mohit - July 8, 2013

Hi i am using your Find all sheet, its very good, but my requirement is for example let suppose correct Item Code number is
( 1288-010-001I) but generaly user find the Item with Item Number with this (1288010001) so it should show 1288-010-001I with all the combination, similarly if user place the Item Number 1288020122 and if correct Item Number is 1288-020-122 so it should lookup and show the result in correct format.

Regards
Mohit

Reply
    Jon Acampora - July 8, 2013

    Hi Mohit,

    Here is a quick solution that does not require the code to be modified.

    You can add a column to your data table that contains the Item Number with no dashes (“-“). This column can easily be created using the SUBSTITUTE function.

    For example, let’s say the first Item Number in your data table is in cell A3. Insert a column to the right of A and type/copy in the following formula (this column can also be to the right of the original data table, just make sure it is included in the SearchRange defined in the FindAll function).

    =SUBSTITUTE(A3, “-“,””)

    The SUBSTITUTE function will replace all the dash characters with nothing. See screenshot below.

    Substitute Function for Find All Form

    The Find All Form will now search both columns A and B, and the user can enter either 1288020122 or 1288-020-122 in the search field. If you don’t want to display both columns, you can make column B very narrow and change the font color to white to match the background. You can NOT hide the column because hidden column are not included in the search.

    Please let me know if this helps. If you are not able to add a column to your data table, then we will have to modify the code.

    Thanks,
    Jon

    Reply
Michael Genrich - June 26, 2013

Hi Jon:

Thanks for the form; it’s just what I was looking for.

It works great when I use it on about 1000 entries, but my data is 30,000 lines long(9 columns) and the search seems to lock up Excel for quite a while – too long to be useable.

Is there something that can be done to make the search faster?

I believe that this search returns data found in all 9 columns, but I’d be happy if it would only be able to search in the first column, if that’s the way to make it work.

I am using Excel 2007.

Thanks.

Reply
    Jon Acampora - June 26, 2013

    Hi Michael,

    Narrowing the search to one column should improve the speed. You can try this by changing one line in the VBA code. Here’s a guide on how to do it.

    1. Open the VBA Editor (Alt+F11 from Excel)
    2. Expand the (Find All Form.xls) project
    3. Expand the Forms folder
    4. Right click on f_FindAll > Select View Code from the menu
    5. Go to line 46 in the code “Set SearchRange = ActiveSheet.UsedRange.Cells”
    6. Change this to “Set SearchRange = ActiveSheet.Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))”
    7. Go back to Excel and launch the Find All Form to test.

    See the screenshot below –

    null

    Link to full image – https://www.excelcampus.com/wp-content/uploads/2013/06/Find-All-Form-Search-1st-Column.png

    The code above sets the search range to search all the used cells in column A. This is denoted by the second 1 in the Cells(1, 1) property. The “1” is specifying the column number. If you wanted to search the data in column B, the search range would look like the following.

    Column B – ActiveSheet.Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
    First 3 columns – ActiveSheet.Range(Cells(1, 1), Cells(Rows.Count, 3).End(xlUp))

    Let me know if this makes your search any faster. If so, we could add a drop-down box to the Find All Form window to select the column to search. This would be a more user friendly alternative to modifying code.

    Let me know if you have questions.

    Thanks,
    Jon

    Reply
Nathan - June 2, 2013

Great site and great info, well done!

I tried to download the “Find All” form but it says it is corrupted when I try to open it and Excel deletes the macro. I have been trying to get this function working for a while so the code would be of great value to me.

If you could help that would be great.

Reply

Leave a Reply: