One cannot search a workbook without opening it. Otherwise see if the following helps.
Sub SearchForG20()
Dim sSearchText As String, sPath As String, sFile As String
Dim wb As Workbook, ws As Worksheet, rg As Range
Application.ScreenUpdating = False
'Change Path, need "\"
sPath = ThisWorkbook.Path & "\"
'Modify "*.xls*" if needed
sFile = Dir(sPath & "*.xls*")
sSearchText = Range("G20")
If Len(sSearchText) = 0 Then Exit Sub
Do While Len(sFile)
If sFile <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(FileName:=sPath & sFile)
If Not wb Is Nothing Then
For Each ws In wb.Worksheets
Set rg = ws.Cells.Find(sSearchText)
If Not rg Is Nothing Then
ws.Activate
rg.Select
Exit Do
End If
Next ws
End If
wb.Close
wb = Nothing
End If
sFile = Dir()
Loop
Application.ScreenUpdating = True
If wb Is Nothing Then
MsgBox "The string: " & sSearchText & " was not found."
Else
MsgBox "The string: " & sSearchText & " was found!!!"
End If
End Sub
Bookmarks