Search open workbooks return results

[searchlight]

(Second attempt to send this through)

I have a workbook named Search.xls

The code below searches through any open workbooks for a string entered through an input form.

The results are returned in a message box for each click that is executed and each positive result. (a lot of clicking)

I would like to have the results of the search returned to the Search.xls workbook with the cell reference in column A and the workbook name it was found in, in column B.

I appreciate any help.

Sub SearchBooks()
SearchWord = InputBox("Enter the string to search for")
For i = 1 To Workbooks.Count
Workbooks(i).Activate
For j = 1 to Sheets.Count
Worksheets(j).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) & ActiveSheet.Name & Chr(13) & Address
Range(Address).Activate
GoTo FindAnother
End If
Next j
NextBook:
Next i
End Sub

 

[Gary McMaster]

Maybe this?

Sub SearchBooks()

Dim oOutput As Range
Dim oSearch As Workbook
Dim oBook As Workbook
Dim oSheet As Worksheet
Dim sStart As String
Dim oWordAddress As Range
Dim sSearchWord As String

Set oSearch = Application.Workbooks("Search.xls")
Set oOutput = oSearch.Worksheets("Sheet1").Range("A1")

sSearchWord = InputBox("Enter the string to search for")

For Each oBook In Application.Workbooks
    For Each oSheet In oBook.Worksheets
        Set oWordAddress = oSheet.Cells.Find(What:=sSearchWord, after:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        
        If Not oWordAddress Is Nothing Then
            sStart = oWordAddress.Address
            Do
               Set oWordAddress = oSheet.Cells.FindNext(oWordAddress)
                oOutput.Value = oWordAddress.Address
                oOutput.Offset(0, 1).Value = oBook.Name
                oOutput.Offset(0, 2).Value = oSheet.Name
                Set oOutput = oOutput.Offset(1, 0)
            Loop While Not oWordAddress Is Nothing And oWordAddress.Address <> sStart
        End If
    Next oSheet
Next oBook

End Sub

 

[searchlight]

Thanks Gary I will give that a try tomorrow! Hope things are going well for you.

 

[searchlight]

Thanks Gary that works great. I tried also to return the results of cell D as part of the search

oOutput.Offset(2, 4).Value = Cells("D")

But end up with a Runtime 13 type mismatch

I assume this is because it is not a part of the string search.

Column D contains times and I would like to capture the cell in line with the string results so the return would be Workbook, Sheet Name, Address, and cell D? which has the time.

Thanks though I ran through the code and understand most of it.

 

[Gary McMaster]

Hi Ray,

oOutput.Offset(2, 4).Value = Cells("D")

I'm not exactly sure what you are after but maybe it is something like this:

oOutput.Offset(0, 3).Value = oWordAddress.Offset(0,3).Value

The above assumes that the words you are finding are always in column A. In that case oWordAddress is a reference to a cell in column A. Then you could use Offset (rows, columns) to get the data in the same row & 3 columns (this example) to the right of column A (col D) and copy it to the 4th column of the current output row.

Gary

 

[searchlight]

Works great! Thanks again!!