Find text

[colinharwood]

Hi All

I have a workbook which contains 12 sheets, is it possible write a macro which will search for a text string which could be on any of the 12 sheets

Thanks a lot

colin

 

[Joe Was]

Try this:


Public Sub FindText()
'Run from standard module, like: Module1.

Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer

myText = InputBox("Enter text to find")

If myText = "" Then Exit Sub

For Each ws In ThisWorkbook.Worksheets
With ws
Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)

If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
foundNum = foundNum + 1
rngNm = .Name
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
thisLoc = rngNm & " " & Found.Address

Sheets(rngNm).Select
Range(Found.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)).Select

myFind = MsgBox("Found one """ & myText & """ here!" & vbCr & vbCr & _
thisLoc, vbInformation + vbOKCancel + vbDefaultButton1, "Your Result!")

If myFind = 2 Then Exit Sub

Set Found = .UsedRange.FindNext(Found)

Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With

Next ws

If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbCr & _
AddressStr, vbOKOnly, myText & " found in these cells"
Else:
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If

End Sub

 

[Belgarth58]

It worked perfectly, thank you for your time and patience. This has saved me a lot of time.

Many Thanks

 

[abee4life]

im getting a problem using this code..

im getting this error after doing a search..

ive only got 1 of everything so it should be nice and easy, but it gives me an error code of

Run-Time Error '1004':

Select method of worksheet class failed

when i debug its this line that keeps popping up

Sheets(rngNm).Select

 

[Joe Was]

Just comment out that line with a single quote.

The reason this is happening is the code is setup for more than one sheet. With one sheet in your Workbook, the "Select" sheet is useless!

This should fix the problem, but to be truly correct you need to remove the Sheet loop!

 

[abee4life]

but ive got 4 sheets...

how can i remove the sheet loop?

 

[Joe Was]

Wait the code should work as posted, if you have more than one sheet!

I thought you said you had one of everything?

You may have pasted the code in the wrong Module?

Did you go to the VBA ToolBar: "Insert" and then select "Module" and then paste the code, or did you paste the code in a Sheet Module?

It needs to go into a Standard Module, like: Module1, not: Sheet1!

 

[abee4life]

thanks Joe.. all sorted now

 

[soulless73]

I'm gonna revive this to ask a question.

How could this be modified to check multiple sheets that were in different workbooks?? (all workbooks are open)

 

[soulless73]

hmm maybe it's not possible?

 

[Joe Was]

This code builds a Search Collection of the subject Folder, so if that Folder has a large number of files, this could take a long time! Once the collection is established the search code is somewhat fast. You can tell when it is searching for your data when the folder bar at the bottom displays the file currently being worked on.

Note the code options, Also: Ctrl + Break will stop the search. When the data is found you have the option of "Cancel" which ends the search at the found data File, Sheet and Cell.


Sub allFilesSearch()
'Standard Module code, like: Module1!
Dim f%, foundNum%
Dim ws As Worksheet
Dim Found As Range
Dim myText$, FirstAddress$, thisLoc$, rngNm$, AddressStr$

myText = InputBox("Enter text to find")

If myText = "" Then Exit Sub

Application.ScreenUpdating = False

With Application.FileSearch
.NewSearch

'Option: Search Sub-Folders as well?
.SearchSubFolders = False 'Option: True or False!

'Option Current Folder or a defined folder?
.LookIn = CurDir
'Or
'.LookIn = "C:\myFolderNameHere"

'Option: Only Search this type of file?
.Filename = "*.xls"

.Execute

For f = 1 To .FoundFiles.Count
Set Wb = Workbooks.Open(Filename:=.FoundFiles(f))

For Each ws In Wb.Worksheets
With ws
Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)

If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
foundNum = foundNum + 1
rngNm = .Name
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
thisLoc = rngNm & " " & Found.Address

Sheets(rngNm).Select
Range(Found.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)).Select

myFind = MsgBox("Found one """ & myText & """ here!" & vbCr & vbCr & _
Wb.Name & ": " & thisLoc, vbInformation + vbOKCancel + vbDefaultButton1, "Your Result!")

If myFind = 2 Then Exit Sub

Set Found = .UsedRange.FindNext(Found)

Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With

Next ws

If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbCr & _
AddressStr, vbOKOnly, myText & " found in these cells"
Else:
'MsgBox "Unable to find " & myText & " in Workbook: " & Wb.Name, vbExclamation
End If

ActiveWorkbook.Save
ActiveWorkbook.Close

Next f
End With

Application.ScreenUpdating = True
End Sub