1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Search Sub

Discussion in 'VBA Macros' started by cyliyu, Sep 14, 2018.

  1. cyliyu

    cyliyu Member

    Messages:
    170
    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.

    Attached Files:

    Last edited: Sep 14, 2018
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,520
    either Unhide the worksheet

    Code (vb):
            '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 (vb):
            '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 (vb):
            '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
     
  3. cyliyu

    cyliyu Member

    Messages:
    170
    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.
  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,520
    Code (vb):
    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
    cyliyu likes this.
  5. cyliyu

    cyliyu Member

    Messages:
    170
    Hi Hui,
    Thanks. The code works. Appreciate.

Share This Page