Find text

colinharwood

Active Member
Joined
Jul 27, 2002
Messages
419
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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
419
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,012
Messages
5,834,909
Members
430,326
Latest member
tomwax46

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