FindAll Add-In
This page describes an Add-In for Excel that allows you to search any
number of worksheets in a workbook and display all of the found results.
Excel 2002 and later versions allow you to search all worksheets in a workbook (an improvement over earlier versions that searched only
on a single worksheet). However, you cannot see all found results together. You can use Find Next to go from one
result to the next result. The FindAll add-in for Excel provides a user interface
allowing you to select search
options, specify one or more worksheets to search, a range of cells to search, and then display all the results
on the screen. The core functionality of the FindAll add-in is
the FindAll VBA procedure described on the FindAll VBA Function page.
The FindAll add-in calls the code described on that page for each worksheet selected
in the FindAll user interface and then aggregates all the results into a single list box.
When you load the FindAll.xla add-in, a new item named Find All... is added to the Edit
menu. When clicked, this item will open the FindAll user interface, shown below.
You use the FindAll interface to configure and execute your search. Enter the text to
search for in the Find What box. Click the button to the right of the Find What box to put
the contents of the Active Cell in the Find What box. To search a specific range, enter that range in the In Range
box. This range applies to all worksheets. To search the entire sheet, leave this box empty. Select the worksheet(s)
to search in the On Sheets list. To search all worksheets, click the Select All button.
Next, set the search options. The Look In values indicate whether to search in the values of
cells (Values option selected) or in the formulas of cells (Formulas option selected). The
Look At option tells the search to look for a match of an entire cell (Whole option selected)
or within all the text in a cell (Part option selected). The Search Order item specifies the
orientation of the search. If the By Row option is selected, the search proceeds across each row and then
move down to the next row. If the By Column option is selected, the search proceeds down each column, moving
one column to the right. The Match Case option indicates whether to ignore differences of upper and lower
case (ABC is equal to aBc) or to take upper and lower case into account during the search (
ABC is not equal to aBc).
Once you have set the search options, click the Find All button to start the search. The found items
will be displayed in the Search Results list. By default, all results for all worksheets are displayed
in the list. You can select a worksheet in the Show Results For Worksheet to display the results for
only that worksheet. Below the Search Results list, there are buttons to delete a selected item
from the results list, to clear the entire results list, to scroll the workbook to an item on the search list,
and to copy the results list Clipboard (as text).
Download the zip file and save it to some folder. Unzip the file with your favorite unzip program,
such as WinZip or SecureZip. In Excel, go to the Tools menu, choose Add-Ins and then click the
Browse button. This will open a standard Windows File Open dialog. Navigate to the folder containing the
FindAll.xla file and then select and open the xla file. When the add-in loads, a new item named
FindAll will be displayed on the Edit menu, just below Excel's built in Find item.
In Excel 2007 and later, the FindAll item will appear on the Add-Ins
ribbon panel.
 |
This page last updated: 13-December-2008. |