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

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

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

On Jan 20, 9:39*am, "Bernard Liengme"
wrote:
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 fromhttp://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 MVPhttp://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- Hide quoted text -

- Show quoted text -

Thank you for your responce....I think I may have to clarify what I am
trying to say. I do nto want to loop through each worksheet to find a
string. I want to be able to use Find in VBA like you can set to look
in workbook instead of work sheet.
Set Sht = Cells.Find(What:=Item, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Worksheet
This errors out and does not set my worksheet variable.

Thanks again,
Jay

Something like this should be close...

Sub FindStuff()
Dim rngFound As Range
Dim wks As Worksheet

For Each wks In Worksheets
Set rngFound = wks.Cells.Find(What:="Tada", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rngFound Is Nothing Then Exit For
Next wks

If rngFound Is Nothing Then
MsgBox "Not Found"
Else
rngFound.Parent.Select
rngFound.Select
End If
End Sub
--
HTH...

Jim Thomlinson


"jlclyde" wrote:

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
.

On Jan 20, 10:03*am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
Something like this should be close...

Sub FindStuff()
* * Dim rngFound As Range
* * Dim wks As Worksheet

* * For Each wks In Worksheets
* * * * Set rngFound = wks.Cells.Find(What:="Tada", _
* * * * * * * * * * * * * * * * * * LookIn:=xlFormulas, _
* * * * * * * * * * * * * * * * * * LookAt:=xlWhole, _
* * * * * * * * * * * * * * * * * * MatchCase:=False)
* * * * If Not rngFound Is Nothing Then Exit For
* * Next wks

* * If rngFound Is Nothing Then
* * * * MsgBox "Not Found"
* * Else
* * * * rngFound.Parent.Select
* * * * rngFound.Select
* * End If
End Sub
--
HTH...

Jim Thomlinson



"jlclyde" wrote:
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
.- Hide quoted text -

- Show quoted text -

Jim,
Thanks for this, it works great.
Jay