Search word in different workbooks and sheets

**k0st4din** · 11-29-2012, 12:01 PM

Hello to all.
I would like to ask you if you find someone who can help me with these two macros that work very well but for certain things. So because I do not understand many will not explain what the problem is:
The first is made so that the popup window to search for a word in countless workbooks, it does but only in the open sheet of the workbook. So you can be less that the revised starting from the first workbook, going through all the paper then goes to the second sheet and check all the way to the end?

Code:

Sub SearchBookss()
SearchWord = InputBox("Enter the string to search for")
For i = 1 To Workbooks.Count
Workbooks(i).Activate
Range("A1").Activate
FindAnother:
Set WordAddress = Cells.Find(What:=SearchWord, after:=ActiveCell, LookIn:=xlFormulas, lookat:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If WordAddress Is Nothing Then
MsgBox ActiveWorkbook.Name & Chr(13) & "Search string not found"
Else:
If WordAddress.Address = CheckCell Then GoTo NextBook
If ActiveCell.Address = "$A$1" Then CheckCell = WordAddress.Address
Address = WordAddress.Address
MsgBox ActiveWorkbook.Name & Chr(13) & Address
Range(Address).Activate
GoTo FindAnother
End If
NextBook:
Next i
End Sub

I want to thank you in advance for all your help.

**Admin** · 11-29-2012, 02:16 PM

Hi k0st4din,

Welcome to ExcelFox!!

Ask the questions one by one.

try this one.

Code:

Sub SearchBookss()
    
    Dim Sht     As Worksheet
    
    SearchWord = InputBox("Enter the string to search for")
    For i = 1 To Workbooks.Count
        For Each Sht In Workbooks(i).Worksheets
FindAnother:
            Set WordAddress = Sht.UsedRange.Find(What:=SearchWord, after:=Sht.Cells(1), LookIn:=xlFormulas, lookat:= _
                            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            If Not WordAddress Is Nothing Then
                If WordAddress.Address = CheckCell Then GoTo NextBook
                If WordAddress.Address = "$A$1" Then CheckCell = WordAddress.Address
                Address = WordAddress.Address
                MsgBox Workbooks(i).Name & Chr(13) & Address
                Sht.Range(Address).Activate
                GoTo FindAnother
            End If
        Next
NextBook:
    Next i
End Sub

**k0st4din** · 11-29-2012, 02:27 PM

**Admin** · 11-29-2012, 03:38 PM

Hi

Dim the variables properly. This should work.

Code:

Sub SearchBookss()
    
    Dim Sht             As Worksheet
    Dim SearchWord      As String
    Dim WordAddress     As Range
    Dim CheckCell       As String
    Dim Addr            As String
    Dim i   As Long
    
    Application.ScreenUpdating = 0
    
    SearchWord = InputBox("Enter the string to search for")
    On Error GoTo Xit
    For i = 1 To Workbooks.Count
        For Each Sht In Workbooks(i).Worksheets
            Application.Goto Sht.Cells(1)
FindAnother:
            Set WordAddress = Sht.UsedRange.Find(What:=SearchWord, lookat:=xlPart, MatchCase:=False)
            If Not WordAddress Is Nothing Then
                CheckCell = WordAddress.Address
                Do
                    Set WordAddress = Sht.UsedRange.FindNext(WordAddress)
                    Addr = WordAddress.Address
                    MsgBox Workbooks(i).Name & Chr(13) & Sht.Name & vbLf & Addr
                Loop Until Addr = CheckCell
                Application.Goto Sht.Range(CheckCell)
            End If
        Next
NextBook:
    Next i
    Application.ScreenUpdating = 1
    Exit Sub
Xit:
    MsgBox Err.Description
    Application.ScreenUpdating = 1
End Sub