Textbox Search Range of Hidden Sheets

Question

This is supposed to loop through the workbook hidden or unhidden sheets, and search for textbox.text in Range I, and K, setting the sheets back to the visibility state they were in originally. Well, it's not working. It glitches out on the foundCell1 line, When I hover over foundCell1, it says Empty. The search Term is Populated correctly. I've unhidden all the sheet manually just to make sure the visibility thing wasn't messing things up, but that didn't solve anything. Thoughts?

Runtime Error 91, Object variable or With Block variable not set

on the foundCell1 line.

    Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = vbKeyReturn Then
            SearchSheets
        End If
    End Sub
    
    Sub SearchSheets()

        Dim ws As Worksheet
        Dim searchTerm As String
        Dim foundCell1 As Range
        Dim foundCell2 As Range
        Dim OrVis As Boolean
    
        searchTerm = Trim(TextBox1.Text)
    
        For Each ws In ThisWorkbook.Worksheets
        
            If InStr(1, ws.Name, "-") > 0 And Not Right(ws.Name, 3) = "MAP" Then
               OrVis = ws.Visible
                ws.Visible = xlSheetVisible
    
                foundCell1 = ws.Range("I:I").Find(searchTerm, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
                foundCell2 = ws.Range("K:K").Find(searchTerm, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
        
                If Not foundCell1 Is Nothing Or foundCell2 Is Nothing Then
                
                    ws.Activate
                    ws.Move After:=Worksheets("Index")
                    If Not foundCell1 Is Nothing Then
                        foundCell1.Select
                    Else
                        foundCell2.Select
                    End If
                    
                    Exit For
                End If
    
                ws.Visible = OrVis
                End If
       
                If foundCell1 Is Nothing And foundCell2 Is Nothing Then
                    MsgBox "Not Found", vbInformation, "Search Results"
                End If
         Next ws

    End Sub

Answer 1

• The Find method can search across multiple columns.

Microsoft documentation:

Range.Find method (Excel)

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        SearchSheets
    End If
End Sub
Sub SearchSheets()
    Dim ws As Worksheet
    Dim searchTerm As String
    Dim foundCell As Range, bFound As Boolean
    searchTerm = Trim(TextBox1.Text)
    For Each ws In ThisWorkbook.Worksheets
        If InStr(1, ws.Name, "-") > 0 And Not Right(ws.Name, 3) = "MAP" Then
            foundCell = ws.Range("I:I, K:K").Find(searchTerm, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not foundCell Is Nothing Then
                Application.ScreenUpdating = False
                ws.Visible = xlSheetVisible
                ws.Move After:=Worksheets("Index")
                ws.Activate
                foundCell.Select
                Application.ScreenUpdating = True
                bFound = True
                Exit For
            End If
        End If
    Next ws
    If Not bFound Then
        MsgBox "Not Found", vbInformation, "Search Results"
    End If
End Sub