Re: Print the results of a search

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Dave Peterson (ec35720_at_netscapeXSPAM.com)
Date: 01/12/05 

Date: Tue, 11 Jan 2005 19:04:00 -0600

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 Work***
    Dim RptWks As Work***
    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("Work*** 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
Quantcast