can I Find something in an entire workbook not just a worksheet?

[fil977]

This is more of an Excell query, but if it can only be done with VB then so be it.

I want to perform a FIND of a particular phrase in an entire workbook not just a single worksheet. Is this possible?

Any pointers would be helpful.

 

[dhulbert]

In the find box select "Options" in there is a within box that lets you choose between serching sheets or workbooks.

ASCII stupid question, get a stupid ANSI!

 

[TonyJollans]

Hi fil977,

It does depend on your version. I think search WorkBook came in with XP (2002).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Loomah]

Hi
Becasue I'm not farmiliar with that option (and now I know why!!) I thought Id see if I caould do somethig that could be built on if necessary

Sub SearchBook()
Dim ws As Worksheet
Dim sFind As String, sRes As String
Dim Found As Range
sFind = InputBox("FIND WHAT????", "FIND")
For Each ws In ThisWorkbook.Worksheets
    Set Found = ws.Cells.Find(sFind)
        If Not Found Is Nothing Then
            sRes = sFind & " found in worksheet " & ws.Name & " cell " & Found.Address
            Exit For
        End If
Next
If sRes = "" Then sRes = sFind & " can't be found in this workbook"
MsgBox sRes
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[fil977]

TonyJollans--you are exactly right I am running 2000 so this option is unavailable.

Loomah--the code looks good. will give it a try as soon as I get the chance.

Thanks guys, just proves once again that my own service desk could do with checking this site!