Find text

colinharwood

Active Member
Joined
Jul 27, 2002
Messages
384
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Yes. I'm not sure what you want to do when you find the text you're searching for, so the procedure below will just flash up a message saying where the text was found. Change myText to the text string you want to find: -<pre>
Public Sub FindText()

Dim ws As Worksheet
Dim Found As Range
Const myText As String = "your search string"
Dim FirstAddress As String
Dim AddressStr As String

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
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
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 AddressStr, vbOKOnly, myText & " found in these cells"
Else:
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If

End Sub</pre>
This message was edited by Mudface on 2002-10-05 11:30
 

colinharwood

Active Member
Joined
Jul 27, 2002
Messages
384
Office Version
  1. 2019
Platform
  1. Windows
Thanks for that Mudface

That will do what I want , but can an input box be incoporated so that different text to search for can be entered easily

Cheers

Colin
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Try this modification: -

<pre>
Public Sub FindText()

Dim ws As Worksheet
Dim Found As Range
Dim myText As String
Dim FirstAddress As String
Dim AddressStr As String

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
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
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 AddressStr, vbOKOnly, myText & " found in these cells"
Else:
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If

End Sub
</pre>
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539

ADVERTISEMENT

Sub myfind()
Dim Message, Title, Default, SearchString
Message = "Enter your search string!" ' Set prompt.
Title = "Find ? On all sheets!" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
SearchString = InputBox(Message, Title, Default)

'SearchString = "Rob"
Set S = Sheets.Application
For Each S In Application.Sheets
With S.Range("A1:IV65536")
Set F = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If F Is Nothing Then
Else
Location = F.Address
S.Select
Range(Location).Select
Exit For
End If
End With
Next S
End Sub
 

27458

Board Regular
Joined
Sep 20, 2004
Messages
71

ADVERTISEMENT

How to modify this to return the total number of occurrences located?
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
I guess "Mudface" os gone?

This should do it using his code:

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

Dim ws As Worksheet, Found As Range
Dim myText As String, FirstAddress 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
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
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
 

n1kki

New Member
Joined
Oct 29, 2003
Messages
22
Hi can you change the codes above so that when it has found where the text is it goes to that sheet in the workbook, rather than just teling you where it is.

thanks nikki
 

Watch MrExcel Video

Forum statistics

Threads
1,127,201
Messages
5,623,343
Members
415,969
Latest member
Rey99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top