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

 

[winrz]

Thanks again Joe, I will give this a shot. This is going to force me to learn how to work these VBA loops...

 

[Mr.G]

Joe you amaze me everytime..

Hi all.

How do I change this to search only the active sheet?

Thanx

 

[Joe Was]

Mr.G

This is a Monster post, with many different twists on the same theme, could you post the code you want to work on only one sheet. Also, I think I did this in this post, above somewhere, once before.

 

[Mr.G]

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 & " on the sheet.", vbExclamation
End If

End Sub

 

[Mr.G]

Sorry having problem lgging in....That is what I'm using. I've changed as I needed.

Thank you

 

[Joe Was]

For questions as simple as this you need to help youself!
Search the Board, search Excel VBA HELP.
All the code was written, you only needed to make one small change to the MsgBox!

I would prefer to help you learn how to do this on your own, so you will only need to post when you are stuck and have looked into the problem on your own, rather than just do your work for you!

Here is the solution:

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
Dim myF As Variant

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

myF = MsgBox("Found one """ & myText & """ here!" & vbCr & vbCr & _
thisLoc, vbInformation + vbOKCancel, "Found!")

'MsgBox myF

If myF = 2 Then GoTo myEnd

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

myEnd:
End Sub

 

[Mr.G]

Sorry about this Joe.
I've been trying to get this on my own. I think I'm not quite that advanced in this Vb thing as I'm trying to teach myself.

Ps. Still searches the whole workbook.

 

[Joe Was]

Sorry too many questions, I thought it was about putting a Cancel button in the Found Box. I see up a few that you want the active sheet. I know I did that for someone else in this post!

Here is how to do it to the one you selected as a model:


You cut out the Sheet loop [ws] and its Next ws and do the same with the "With ws, End With"

Then infront of every leading "." add "ActiveSheet" and you are done!


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
Dim myF As Variant

myText = InputBox("Enter text to find")

If myText = "" Then Exit Sub

Set Found = ActiveSheet.UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)

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

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

myF = MsgBox("Found one """ & myText & """ here!" & vbCr & vbCr & _
thisLoc, vbInformation + vbOKCancel, "Found!")

If myF = 2 Then GoTo myEnd

Set Found = ActiveSheet.UsedRange.FindNext(Found)

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

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

myEnd:
End Sub

 

[Mr.G]

Ahhh........
".UsedRange" Didn't think to add that in the module.
Thank you Joe

 

[Joe Was]

"Find" only works on a Range. You can define the range a number of ways, but not all Range representations will work. So a short-cut to a usable Range is the ActiveSheet's "UsedRange" object. In some cases this may miss some data if your data is arranged using a lot of cell white space. For that type of sheet you need to define the range to search a different way.