How To Search For A Word Within A Workbook

[aedctalk]

Hi,

Wondering if i had a serach term in L3 like "uniforms"

are there any macros out there.. or any way possible to search all sheets of a workbook and then have it take me to the row and sheet that each instance occurs?

or if easier. something that would find those words and then copy all rows that have that word or phrase into a empty sheet called search results?


Just curious if there is anything like that out there? Or if its even a possiblity with excel/vba?

Thanks so much!

 

[Tom Urtis]

You can do this manually quickly...right click any sheet tab, left click on Select All Sheets, hit Ctrl+F, enter your search word, and click Find Next. That would find teh word(s). The macro would be needed to do a copy and paste, so post back if you need that, such as if this is a recurring task, or if just selecting Select All Sheets does it for you. PS, remember to ungroup the sheets when you are done.

 

[Vladislav]

Try this in the code window of the worksheet with L3:
Make sure you're not using the first two columns for anything, otherwise change range("A1") to where you'd like the output to go.

Sub SearchForWord()

Dim ws As Worksheet
Dim c As Range
Dim i As Integer
Dim strSearch As String

strSearch = Range("L3")
i = 1
With Range("A1")
For Each ws In ThisWorkbook.Worksheets
For Each c In ws.UsedRange
If c = strSearch Then
.Cells(i, 1) = c.Parent.Name
.Cells(i, 2) = c.Address
i = i + 1
End If
Next c
Next ws
End With

End Sub

 

[Vladislav]

 

[aedctalk]

Thank you for your help you guys!


I tried the code however on this part



If c = strSearch Then

I get runtime error 13 "type Mismatch"


Thanks so much for code! What exactly will it do?

 

[Vladislav]

Try c.value instead of c to make it explicit, perhaps that's your error 13 problem. It seems not to care with my version of Excel.

The Find method would be quicker but involve more code, depends how big the used ranges are in your worksheets.

Let me know your Excel version if it still doesn't work.

 

[Vladislav]

Another idea is to try ws.UsedRange.Cells instead of ws.UsedRange if it still doesn't work for you.