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

 

[owendodd]

I was wondering how using my code could i restrict the search to a column.

Thnaks for your assistance

 

[Joe Was]

The part of the code that starts with:

Set Found = .UsedRange


Replace the UsedRange with the Column Range you want to search!

Do this for all the "UsedRange" references.

 

[owendodd]

Hey,

I tried that as shown, changing Range("A1:A400") as shown and now i don't seem to be able to retrieve anything. It comes up blank all the time.



With ws

Set Found = Range("A1:A400").Find(what:=myText, lookAt:=xlPart, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
CodeStr = Found.Offset(, 1).Value & " refering to the worldwide standards " & Found.Offset(, 2).Value
If Len(CodeStr) = 0 Then CodeStr = "blank."
Do
foundNum = foundNum + 1
rngNm = .Name
AddressStr = AddressStr & .Name & " " & " Document Name is " & CodeStr & vbCrLf
thisLoc = rngNm & " " & Found.Address
'ActiveSheet.Select
Sheets(rngNm).Select
Range(Found.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)).Select
If myFind = 1 Then Exit Sub

Set Found = Range("A1:A400").FindNext(Found)
CodeStr = Found.Offset(, 1).Value & Found.Offset(, 2).Value

 

[excel_beg]

Finding text

Hi,
I have looked at the code examples here. The first example does work, but I want to search whole words only.
Eg currently a search for 'ratio' finds calibration too.
How can I do a search only for whole word ?

 

[owendodd]

Joe has in one of his examples above

Set Found = .UsedRange.Find(what:=myText, lookAt:=xlWhole, MatchCase:=False)

You change lookAt:=xlPart to lookAt:=xlWhole

Hope this helps

 

[excel_beg]

changed lookAt:=xlWhole

Yes, I saw that message, and tried the change.
When I did that, nothing was being returned, even after searching for a string that I know was in the sheet.

 

[Joe Was]

"xlWhole" does not mean the whole word, it means the Search string is the whole contents of the searched cell. If the cell contains any additional information beyond the search string, even a space at the end it will fail!

 

[Joe Was]

To search columns defined by your range you must indicate the Sheet!

Add: ws
to the front of your range.

Like: ws.Range("A1:A400")

 

[owendodd]

Joe thanks for your help, I was wondering if you could glance over my code for a second. The results work fine when i find one result, eveything come up fine.

The output comes out fine. It will say The Document (offset to -1 cell) will refer to (offset to -2).

I am currently having a problem whereby it says 14 results are found and it displays only say 10 on the pop up message dialog. The last message it leaves say there The document and leaves the rest as blank. It also leaves the part where i have "refer to" in some of the results it finds in the msgbox.

Another problem is when i drag the dialog box, it drags all the way around the screen as like there is a problem with memory.

Would there be a mistake as having a loop in the wrong place.

Cheers!



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


myText = ActiveSheet.Range("B7").Value

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 = "S:\Document\Database"


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


.Execute

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

For Each ws In Wb.Worksheets

With ws

Set Found = .UsedRange.Find(what:=myText, LookAt:=xlWhole, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
CodeStr = Found.Offset(0, -1).Value & " refers to " & Found.Offset(0, -2).Value

If Len(CodeStr) = 0 Then CodeStr = "blank."
Do
foundNum = foundNum + 1
rngNm = .Name
AddressStr = AddressStr & .Name & " " & " Document Title is " & CodeStr & vbCrLf
thisLoc = rngNm & " " & Found.Address
ActiveSheet.Select
Sheets(rngNm).Select
Range(Found.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)).Select
If myFind = 1 Then Exit Sub

Set Found = .UsedRange.FindNext(Found)
CodeStr = Found.Offset(0, -1).Value & Found.Offset(0, -2).Value


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

If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " time(s)." & vbCr & _
AddressStr, vbOKOnly, myText & " has been found"
Else:
MsgBox "Unable to find " & myText & " in Workbook: " & Wb.Name, vbExclamation
End If

ActiveWorkbook.Close

Next f
End With

Application.ScreenUpdating = True
End Sub

 

[excel_beg]

Do whole word search

Thanks for the clarification regarding xlWhole. Is it not possible to do a whole word search even if the cell contains other words ?
Eg I should be able to find 'ratio' in a cell that has 'main ratio' in it.

How can this be done ? Cells that contain 'calibration' should not be returned.