If the text is PART of the cell's contents (e.g Cell has "green apple" and
you are searching for "apple")
Replace
If myCell.Value = WhatText Then
By
If InStr(myCell.Value, WhatText) Then

Bernard

"Bernard Liengme" wrote in message
...
This worked for me.

Option Explicit
Dim WhatText
Sub LookAtSheets()
Dim MySheet
WhatText = InputBox(Prompt:="What text are you seeking?", _
Title:="Enter Text")
For Each MySheet In Worksheets
' MsgBox MySheet.Name
MySheet.Activate
FindUsedRange
Next
End Sub

Sub FindUsedRange()

Dim Rng1 As Range
Dim myCell As Range

Set Rng1 = RealUsedRange
If Rng1 Is Nothing Then
MsgBox "There is no used range, the worksheet is empty."
Else
' MsgBox "The real used range is: " & Rng1.Address
For Each myCell In RealUsedRange
If myCell.Value = WhatText Then
MsgBox "Found " & ActiveSheet.Name & " " & myCell.Address
Exit Sub
End If
Next
End If

End Sub
' This codes is from http://www.vbaexpress.com/kb/getarticle.php?kb_id=82
Public Function RealUsedRange() As Range

Dim FirstRow As Long
Dim LastRow As Long
Dim FirstColumn As Integer
Dim LastColumn As Integer

On Error Resume Next

FirstRow = Cells.Find(What:="*", After:=Range("IV65536"),
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

FirstColumn = Cells.Find(What:="*", After:=Range("IV65536"),
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

LastColumn = Cells.Find(What:="*", After:=Range("A1"),
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Set RealUsedRange = Range(Cells(FirstRow, FirstColumn), Cells(LastRow,
LastColumn))

On Error GoTo 0

End Function

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"jlclyde" wrote in message
...
I am having trouble using find to locate a string in a workbook. I
want to be abel to find the value anywhere in the workbook. It will
only be listed once.

Any help will be greatly appreciated,
Jay