Find Value Across All Worksheets

nightkil · Mar 21st 2008

Hi,

I'm trying to use a simple for to search the entire workbook for 1 value. Below you will have the code I currenlty have but I keep getting an error stating that "Oject variable or With block variable not set". Can someone point me in the right direction please. Thank you in advance.

The code in yellow is what "debug" highlights as the issue.

Code

Option Explicit
Dim oWs        As Worksheet
Dim rSearch    As Range
Dim rCl        As Range
Dim sFind      As String
Dim sFirstAddress As String


Private Sub cmdFinish_Click()
    Unload Me
End Sub


Private Sub cmdSearch_Acive_Click()
    If Me.tbxFind.Value = "" Then
        MsgBox "No search item entered"
        Me.tbxFind.SetFocus
        Exit Sub
    Else: sFind = Me.tbxFind.Value
    End If


    If IsNumeric(sFind) Then
        Set rSearch = oWs.Range("B2:B500")
      End If
    'find it
    With rSearch
       [COLOR="Yellow"] Set rCl = .Find(sFind, LookIn:=xlValues)[/COLOR]
        If Not rCl Is Nothing Then
            Select Case MsgBox(sFind & " here: " & oWs.Name & " : " & _
                               rCl.Address & ". Do you want to modify the data?", vbYesNo Or _
                                                                                  vbQuestion Or vbDefaultButton1, "Success")


                Case vbYes
                    rCl.Select
                    End
                Case vbNo
                    'continue search of sheet
                    sFirstAddress = rCl.Address
                    Do
                        'colour cell if you want
                        rCl.Interior.ColorIndex = 3
                        Set rCl = .FindNext(rCl)
                    Loop While Not rCl Is Nothing And rCl.Address <> sFirstAddress


            End Select
        End If
    End With


End Sub


Private Sub cmdSearch_All_Click()


    If Me.tbxFind.Value = "" Then
        MsgBox "No search item entered"
        Me.tbxFind.SetFocus
        Exit Sub
    Else: sFind = Me.tbxFind.Value
    End If


    For Each oWs In ActiveWorkbook.Worksheets
        'check for entries in sheets
        If Application.WorksheetFunction.CountA(oWs.Cells) > 0 Then
            'determine range to search
            If IsNumeric(sFind) Then
                Set rSearch = oWs.Range("B2:B500")
            End If
            'find it
            With rSearch
              [COLOR="yellow"]  Set rCl = .Find(sFind, LookIn:=xlValues)[/COLOR]
                If Not rCl Is Nothing Then
                    Select Case MsgBox(sFind & " here: " & oWs.Name & " : " & _
                                       rCl.Address & ". Do you want to modify the data?", vbYesNo _
                                                                                          Or vbQuestion Or vbDefaultButton1, "Success")


                        Case vbYes
                            rCl.Select
                            End
                        Case vbNo
                            'continue search of sheet
                            sFirstAddress = rCl.Address
                            Do
                                'colour cell if you want
                                rCl.Interior.ColorIndex = 3
                                Set rCl = .FindNext(rCl)
                            Loop While Not rCl Is Nothing And rCl.Address <> sFirstAddress


                    End Select
                End If
            End With
        End If
    Next oWs
End Sub

Display More

Simon Lloyd · Mar 21st 2008

Re: Form To Seach Workbook For A Value

That seems a lot of code just to find a value!, try this:

Code

Sub Macro1()
Dim IB As String
Dim rFound As String
IB = InputBox("Enter Value to find", "Value to edit")
If IB = "" Then Exit Sub
For Each Sheet In Sheets
With Sheets(Sheet.Name)
If Sheets(Sheet.Name).Name = "" Then Exit Sub
 rFound = .Cells.Find(What:=IB, After:=.Range("A1"), LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Address
        If .Range(rFound).Value = "" Then GoTo Nxt
   If MsgBox("Do you want to edit the data?", vbYesNo, "Data Editing") = vbYes Then
   Sheets(Sheet.Name).Select
   Range(rFound).Select
   Exit Sub
   Else: Exit Sub
   End If
   End With
Nxt:
   Next
End Sub

Display More

run the macro, enter the word or number or whatever to look for and take it from there, you should be able to modify it!

nightkil · Mar 21st 2008

Re: Form To Seach Workbook For A Value

Thanks for the reply.

I tried the code you posted but I get the same error message. Would it had anything to do that what I'm trping is not an exact match to the cell value? (It would be names of reports ei. I would search for Basedata but the reports would actually be call Basedata Quarterly). And this might not be possible but I was also looking to it setup that when I enter a value to search to have excel bring me to the sheet and high light the row it found. and maybe have another box come up (something like "Next") just in case that is not the report I'm looking for.

Again thanks for your help.

Code

Sub Macro1()
    Dim IB As String
    Dim rFound As String
    IB = InputBox("Enter Value to find", "Value to edit")
    If IB = "" Then Exit Sub
    For Each Sheet In Sheets
        With Sheets(Sheet.Name)
            If Sheets(Sheet.Name).Name = "" Then Exit Sub
            [COLOR="Orange"]rFound = .Cells.Find(What:=IB, After:=.Range("A1"), LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Address[/COLOR]            If .Range(rFound).Value = "" Then GoTo Nxt
            If MsgBox("Do you want to edit the data?", vbYesNo, "Data Editing") = vbYes Then
                Sheets(Sheet.Name).Select
                Range(rFound).Select
                Exit Sub
            Else: Exit Sub
            End If
        End With
Nxt:
    Next
End Sub

Display More

Simon Lloyd · Mar 21st 2008

Re: Form To Seach Workbook For A Value

I just used the code below i had a cell with two words in one of which i was looking for, in the input box i entered the word and it found it no problem, i have now fixed it so that it highlights the entire row.

Code

Sub Macro1()
    Dim IB As String
    Dim rFound As String
    IB = InputBox("Enter Value to find", "Value to edit")
    If IB = "" Then Exit Sub
    For Each Sheet In Sheets
        With Sheets(Sheet.Name)
            If Sheets(Sheet.Name).Name = "" Then Exit Sub
    rFound = .Cells.Find(What:=IB, After:=.Range("A1"), LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Address
            If .Range(rFound).Value = "" Then GoTo Nxt
            If MsgBox("Do you want to edit the data?", vbYesNo, "Data Editing") = vbYes Then
                Sheets(Sheet.Name).Select
                Range(rFound).EntireRow.Select
                Exit Sub
            Else: Exit Sub
            End If
        End With
Nxt:
    Next
End Sub

Display More

nightkil · Mar 21st 2008

Re: Form To Seach Workbook For A Value

That code just does not seem to work for me... I keep getting the same error.

but it seems I found a code in anouther thread that actually is doing most of what I need.

Code

Dim ThisAddress$, Found, FirstAddress
    Dim Lost$, N&, NextSheet&
    Dim CurrentArea As Range, SelectedRegion As Range
    Dim Reply As VbMsgBoxResult
    Dim FirstSheet As Worksheet
    Dim Ws As Worksheet
    Dim Wks As Worksheet
    Dim Sht As Worksheet
     
    Set FirstSheet = ActiveSheet '< bookmark start sheet
    Lost = InputBox(prompt:="What are you looking for?", _
    Title:="Find what?", Default:="*")
    If Lost = Empty Then End
    For Each Ws In Worksheets
        Ws.Select
        With ActiveSheet.Cells
            Set FirstAddress = .Find(What:=Lost, LookIn:=xlValues)
            If FirstAddress Is Nothing Then '< blank sheet
                GoTo NextSheet
            End If
            FirstAddress.CurrentRegion.Select
            Selection.Interior.ColorIndex = 6 '< yellow
             '//colour the 'Lost' font red, cell colour blank
            With Selection
                Set Found = .Find(What:=Lost, LookIn:=xlValues)
                If Not Found Is Nothing Then
                    FirstAddress = Found.Address
                    Do
                        Found.Interior.ColorIndex = 3 '< red
                        Found.Font.Bold = True
                        Found.Font.ColorIndex = 2
                        Set Found = .FindNext(Found)
                     Loop While Not Found Is Nothing And Found. _
                    Address <> FirstAddress
                End If
            End With
            Reply = MsgBox("Is this the " & Lost & " you're looking for?", _
            vbQuestion + vbYesNoCancel, "Current Region")
             '//restore the 'Lost' font and cell colour
            Set Found = .Find(What:=Lost, LookIn:=xlValues)
            If Not Found Is Nothing Then
                FirstAddress = Found.Address
                Do
                    Found.Font.Bold = False
                    Found.Font.ColorIndex = 0
                    Set Found = .FindNext(Found)
                Loop While Not Found Is Nothing And Found. _
                Address <> FirstAddress
            End If
             '//restore the selection colour
            Selection.Interior.ColorIndex = xlNone
            Set FirstAddress = .Find(What:=Lost, LookIn:=xlValues)
            If Reply = vbCancel Then End
             '//dont look further
            If Reply = vbYes Then
                Set SelectedRegion = Selection
GoTo Finish:
            End If
             '// case=not this one
            ThisAddress = FirstAddress.Address
            Set CurrentArea = Selection
            Do
                If Intersect(CurrentArea, Selection) Is Nothing Then
                    With Selection.Interior
                        .ColorIndex = 6
                        .Pattern = xlSolid
                    End With
                     '//colour the 'Lost' font red, cell colour blank
                    With Selection
                        Set Found = .Find(What:=Lost, LookIn:=xlValues)
                        If Not Found Is Nothing Then
                            FirstAddress = Found.Address
                            Do
                                Found.Interior.ColorIndex = 3
                                Found.Font.Bold = True
                                Found.Font.ColorIndex = 2
                                Set Found = .FindNext(Found)
                            Loop While Not Found Is Nothing And Found. _
                            Address <> FirstAddress
                        End If
                    End With
                    Reply = MsgBox("Is this the " & Lost & " you're looking for?", _
                    vbQuestion + vbYesNoCancel, "Current Region")
                     '//restore the 'Lost' font and cell colour
                    Set Found = .Find(What:=Lost, LookIn:=xlValues)
                    If Not Found Is Nothing Then
                        FirstAddress = Found.Address
                        Do
                            Found.Font.Bold = False
                            Found.Font.ColorIndex = 0
                            Set Found = .FindNext(Found)
                        Loop While Not Found Is Nothing And Found. _
                        Address <> FirstAddress
                    End If
                     '//restore the selection colour
                    Selection.Interior.ColorIndex = xlNone
                    Set FirstAddress = .Find(What:=Lost, _
                    LookIn:=xlValues)
                    If Reply = vbCancel Then End
                    If Reply = vbYes Then
                        Set SelectedRegion = Selection
GoTo Finish:
                    End If
                End If
                If CurrentArea Is Nothing Then
                    Set CurrentArea = Selection
                Else
                    Set CurrentArea = Union(CurrentArea, Selection)
                End If
                Set FirstAddress = .FindNext(FirstAddress)
                FirstAddress.CurrentRegion.Select
            Loop While Not FirstAddress Is Nothing And FirstAddress. _
            Address <> ThisAddress
        End With
NextSheet:
    Next Ws
Finish:
    If Reply = vbYes Then
        Exit Sub
    Else
        FirstSheet.Select
        MsgBox "Search Completed - Sorry, no more " & Lost & "s", _
        vbInformation, "No Region Selected"
    End If

Display More

Thanks for your help.

Sicarii · Mar 21st 2008

Re: Form To Seach Workbook For A Value

There are a couple of reasons you could be getting errors. Add a period into:

Code

Range(rFound).EntireRow.Select

Also, if the value you are looking for isn't on the active sheet the rfound would error so I threw in:

Code

On Error Resume Next

Code

Sub Macro1()
    Dim IB As String
    Dim rFound As String
    On Error Resume Next
    
    IB = InputBox("Enter Value to  find", "Value to edit")
    If IB = "" Then Exit Sub
    For Each Sheet In Sheets
        With Sheets(Sheet.Name)
            If Sheets(Sheet.Name).Name = "" Then Exit Sub
            rFound = .Cells.Find(What:=IB, After:=.Range("A1"), LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Address
            If .Range(rFound).Value = "" Then GoTo Nxt
            If MsgBox("Do you want to edit the data?", vbYesNo, "Data Editing") = vbYes Then
                Sheets(Sheet.Name).Select
                .Range(rFound).EntireRow.Select
                Exit Sub
            Else: Exit Sub
            End If
        End With
Nxt:
    Next
End Sub

Display More

Dave Hawley · Mar 22nd 2008

Re: Find Value Across All Worksheets

Time to read-up on the FIND method.

Find Value Across All Worksheets

Participate now!

Share