ExcelBanter - Return position for each matching value in entire workbook?

You don't say how many typical finds you expect. In extremis, if you
want to list all matches on a single sheet then you could record 65536
times 256, matches. What happens if the rest of the 255 sheets contain
more matches than can be recorded on one sheet.

However if your matches are likely to be more modest, no more than
65535 then the following macro will list the sheet name and the cell
addresses in a sheet called "List"

Watch the word wrap in the line beginning "Worksheets("List")", it's
all one line and ends with "& stAddress"

The value this finds is a string called "test". See the first line of
code. You'll probably want to modify the macro with an input box so
that you can easily change this at runtime.

HTH

Sub MyFind()
Dim stFirstAddress As String, stAddress As String
Dim vaFind As Variant
Dim iSheets As Integer
Dim x As Integer

vaFind = "test"
iSheets = ActiveWorkbook.Sheets.Count

For x = 1 To iSheets
If Worksheets(x).Name < "List" Then
Worksheets(x).Activate
On Error Resume Next
Cells.Select
stFirstAddress = Selection.Find(What:=vaFind,
After:=Range("A1")).Address
Selection.Find(What:=vaFind, After:=ActiveCell).Activate

Do Until stAddress = stFirstAddress
stAddress = ActiveCell.Address
Worksheets("List").Range("A65536").End(xlUp).Offse t(1,
0) = Worksheets(x).Name & " " & stAddress
Selection.Find(What:=vaFind,
After:=ActiveCell).Activate
stAddress = ActiveCell.Address
Loop
End If
Next
End Sub

On Fri, 15 Sep 2006 11:42:01 -0700, Squidman
wrote:

Is there a function or command for a cell in a worksheet that results in a
search of the entire Excel workbook and which then returns (in the worksheet)
the full value / content of each cell in the workbook that matches the
criteria of the search. For example, on one worksheet I want to see every
cell (in its entirety) of the workbook that contains a particular number or
phrase.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________