Search The Matching Data Within Entire Workbook

         A useful Excel VBA Data Search example.       


        In our Excel worksheet , when the cell that to be searched is double-clicked , immediate search process is performed with the loaded userform for value in cell .
     
        When the userform is loaded ,  the matching cells  can be seen as page name and cell address on the listbox control of the userform.
The quantity of matching - found values  are shown on the label at the bottom of the listbox.

excel vba search

         When the items on the listbox are clicked, worksheet and cell where the item is located are selected. We used ListBox1_Click method for this operation :
Private Sub ListBox1_Click()
Sheets(ListBox1.Column(0)).Activate
Range(ListBox1.Column(1)).Select
End Sub

         The section ListBox1.Column (0) in the above code refers to first column of the listbox. In the first column,  sheet names of the found values are shown.
ListBox1.Column (1) in the code refers to second column of the listbox. Cell addresses of the found values are shown in the second column.

 💡 You can adapt this template to your own file. For this, follow the instructions below :
      Open your own file
      Press Alt+F11 keys and open VBE Window
      In left side of VBE window, drag - drop the userform in the template to your own workbook.
      Copy VBA code in the ThisWorkBook field and paste it into this section of your own file :
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) If Target <> "" Then Cancel = True
search_cell_value.Show 0
End If
End Sub

search matching data


No comments:

Post a Comment