Print the results of a search
 

After I do a search via the Tools menu, I want to print the results of the
search. How do I do that?

Edit|find????

There's nothing built into excel that does this:

But you could use a macro...

Option Explicit
Sub testme01()

Dim curWkbk As Workbook
Dim wks As Worksheet
Dim RptWks As Worksheet
Dim oRow As Long

Dim FoundCell As Range
Dim FirstAddress As String
Dim FindWhat As String

FindWhat = InputBox(Prompt:="Find What?")
If FindWhat = "" Then
Exit Sub
End If

Set curWkbk = ActiveWorkbook
Set RptWks = Workbooks.Add(1).Worksheets(1)

With RptWks
.Range("a1").Resize(1, 4).Value _
= Array("Worksheet Name", "Address", "Value", "Formula")
End With

oRow = 1
For Each wks In curWkbk.Worksheets
With wks.Cells
Set FoundCell = .Find(what:=FindWhat, lookat:=xlPart, _
LookIn:=xlFormulas, _
after:=.Cells(.Cells.Count), _
searchdirection:=xlNext, MatchCase:=False)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
oRow = oRow + 1
With RptWks.Cells(oRow, "A")
.Value = "'" & FoundCell.Parent.Name
.Offset(0, 1).Value = FoundCell.Address
With .Offset(0, 2)
.Value = FoundCell.Value
.NumberFormat = FoundCell.NumberFormat
End With
If FoundCell.HasFormula Then
.Offset(0, 3).Value = "'" & FoundCell.Formula
End If
End With
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With
Next wks

End Sub

It does the same as edit|find. Which means that it won't find stuff in cells
hidden by an autofilter (for example).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Celestina wrote:

After I do a search via the Tools menu, I want to print the results of the
search. How do I do that?

--

Dave Peterson