Find a value in a workbook VBA

  • Thread starter Thread starter jlclyde
  • Start date Start date
J

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
 
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
 
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
 
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 -
Click to expand...

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
 
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 -
Click to expand...

Jim,
Thanks for this, it works great.
Jay
 
Back
Top