Find a value in a workbook VBA

[jlclyde]

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

 

[Bernard Liengme]

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]

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

 

[jlclyde]

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







- 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

 

[Jim Thomlinson]

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

 

[jlclyde]

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






- Show quoted text -

Jim,
Thanks for this, it works great.
Jay