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]

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

MsgBox "Found one """ & myText & """ here!" & vbCr & vbCr & _
thisLoc

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]

It works great

thanks

nikki

 

[Belgarth58]

Hi, I only have one problem with this, it finds the text but i get a error message "Microsoft Visual Basic" with a red cross and the number 400,
Any ideas please ???

 

[Joe Was]

On the sheets Excel Toolbar:

Tools - Macro - Security...

Trusted Publishers: Make sure you have checked the box:

Trust access to visual basic project!

 

[MasterChief]

I have tried using this, and for some reason it will only find text on sheet 1 only ???

and then it stops and needs to debug to this point :

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

As a newbie I`m not sure what the problem is, can you help please.

 

[Joe Was]

I think you put the code in the wrong module?

Did you insert a standard module using the VBA ToolBar: Insert - Module?

 

[MasterChief]

Opps.... my mistake, works perfectly now.

 

[MasterChief]

Last question...I promise, how do i add a cancel button when it finds what I`m looking for. Its only because sometimes i have more then one occurance of the text ie 15 - 25. And i cannot stop it searching till it has found all of them.

Many, many thanks

 

[Joe Was]

The simple way is to hit: Ctrl+Break on the key-board when the found message pops up for the one you want. Then click "End."

If this is not good enough we can work on a different way to code the find, so it also has an end option?

The easy way to do this is to put a MsgBox in after each item found asking if you want to continue or quit?

The problem is this would add extra user clicks.

Another way is to trap a key [OnKey Method], that when pressed will end the Sub, using a change event to load it and a global variable test before the next find runs.

 

[Belgarth58]

Any would be good, but it would be good to just have a cancel button on the msgboc, or is that to hard ???