• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search Sub

cyliyu

Member
I have a search function in my Excel sheet.
The code was ok and it will highlight the cell in yellow if match. but after the search, it will go back to the dashboard sheet and results are unknown unless I check sheet by sheet, I have about 100 sheets.
How can I:-
1) Unhide the sheets where the result was found. OR
2) Display a box showing all the sheet name and row,column where the result was found.
 

Attachments

  • list test.xlsm
    46.6 KB · Views: 8
Last edited:
either Unhide the worksheet

Code:
        'Check it found something.
        If Not FoundCell Is Nothing Then
            'Save the first address as FIND loops around to the start
            'when it can't find any more.
            FirstAddress = FoundCell.Address
            wrkSht.Visible = xlSheetVisible 'Unhide worksheet where found
            Do
                With FoundCell.Interior
                    .ColorIndex = 6
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                  
                End With
                'Look for the next instance on the same sheet.
                Set FoundCell = wrkSht.Cells.FindNext(FoundCell)
            Loop While FoundCell.Address <> FirstAddress
        End If

or

Display a message
Code:
        'Check it found something.
        If Not FoundCell Is Nothing Then
            'Save the first address as FIND loops around to the start
            'when it can't find any more.
            FirstAddress = FoundCell.Address
          
            Dim msg As String
            msg = FindString & " found in the " & wrkSht.Name & " worksheet"
            msg = msg & vbCr & "in cell" & FirstAddress
            answer = MsgBox(msg, vbInformation, "Item Found") 'Found message
            Do
                With FoundCell.Interior
                    .ColorIndex = 6
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                  
                End With
                'Look for the next instance on the same sheet.
                Set FoundCell = wrkSht.Cells.FindNext(FoundCell)
            Loop While FoundCell.Address <> FirstAddress
        End If

or

Code:
        'Check it found something.
        If Not FoundCell Is Nothing Then
            'Save the first address as FIND loops around to the start
            'when it can't find any more.
            FirstAddress = FoundCell.Address
           
            Dim msg As String
            msg = FindString & " found" & vbCr
            msg = msg & "Worksheet: " & wrkSht.Name & vbCr
            msg = msg & "Cell : " & FirstAddress
            answer = MsgBox(msg, vbInformation, "Item Found") 'Found message
            Do
                With FoundCell.Interior
                    .ColorIndex = 6
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                   
                End With
                'Look for the next instance on the same sheet.
                Set FoundCell = wrkSht.Cells.FindNext(FoundCell)
            Loop While FoundCell.Address <> FirstAddress
        End If
 
Thanks, Hui.
I missed a point that I can't get it works.
That was when no data was found. a message box with "No Data Found" will be display.
 
Code:
Sub find_highlight()

    'Put Option Explicit at the top of the module and
    'Declare your variables.
    Dim FindString As String
    Dim wrkSht As Worksheet
    Dim FoundCell As Range
    Dim FirstAddress As String
    Dim CountFound As Integer
    CountFound = 0
  
    FindString = InputBox("Please Key in the Number You Wish to Search")
    If FindString = "" Then Exit Sub

    'Use For...Each to cycle through the Worksheets collection.
    For Each wrkSht In ThisWorkbook.Worksheets
        'Find the first instance on the sheet.
        Set FoundCell = wrkSht.Cells.Find(What:=FindString, After:=wrkSht.Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False)
          
'Check it found something.
      If Not FoundCell Is Nothing Then
            'Save the first address as FIND loops around to the start
          'when it can't find any more.
          FirstAddress = FoundCell.Address
        
            Dim msg As String
            msg = FindString & " found" & vbCr
            msg = msg & "Worksheet: " & wrkSht.Name & vbCr
            msg = msg & "Cell : " & FirstAddress
            answer = MsgBox(msg, vbInformation, "Item Found") 'Found message
            CountFound = CountFound + 1
          Do
                With FoundCell.Interior
                    .ColorIndex = 6
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                
                End With
                'Look for the next instance on the same sheet.
              Set FoundCell = wrkSht.Cells.FindNext(FoundCell)
            Loop While FoundCell.Address <> FirstAddress
        End If

    Next wrkSht
    On Error GoTo 0
  
    Select Case CountFound
    Case 0
      msg = "No data found"
    Case Is > 0
      msg = CStr(CountFound) & " matches found"
    End Select
  
    answer = MsgBox(msg, vbInformation, "Finished searching") 'Found message
  
End Sub

Enjoy
 
Dear Hui,
one more request, as some of my spreadsheets obtain thousand over data/rows, how to hide those rows not found (meaning only display those rows match the search condition).

Code:
Rows.EntireRow.Hidden = False

not sure using this code to unhide when returning to the dashboard sheet is a good practice?
 
Last edited:
Back
Top