How to find a string in the entire workbook

[ThrottleWorks]

Hi,
I want to find a string in the entire workbook. Following code helps finding only in one sheet.

The string might be present in any sheet, so I have to search the entire workbook.
For example, I have 20 sheets in a book but I am not sure in which sheet the string is present. So need to search till I get result.

Can anyone please help me in this.

On Error Resume Next
                Cells.Find(What:=Class, After:=ActiveCell, LookIn:= _
                xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False).Activate
On Error GoTo 0

 

[NARAYANK991]

Hi Sachin ,

You can put the posted code within a For ... Next loop as in :

Public Sub Find_text()
           searchtext = "Tentacle"                 ' Change as required
           For Each ws In Worksheets
               ws.Activate
               Set found = ws.Cells.Find(What:=searchtext, After:=ActiveCell, LookIn:= _
                xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
               If Not (found Is Nothing) Then
                  found.Select
                  Exit For
               End If
           Next
End Sub

Narayan

 

[ThrottleWorks]

Thank you very much Narayan Sir. It is working perfectly.
Have a nice day ahead.