Find a value in a workbook VBA
 

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

Find a value in a workbook VBA
 

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

Find a value in a workbook VBA
 

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

Find a value in a workbook VBA
 

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

Find a value in a workbook VBA
 

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
.

Find a value in a workbook VBA
 

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