Search and Populate Matching Text

If you need to search your text and populate a list box based on the matching texts then here is a simple trick.

The list box will refresh after every new character typed in the text box and populate the list box with all matching characters.

The same can be used to populate a combo box or storing the matching texts in an array.

Data :

Fig: Data Structure

Search :

Fig: Search Sheet

Code :


Private Sub txt_item_Change()
    
    If txt_item.Text = "" Then
        lb_item.Clear
        search.Range("F2").Value = 0
        Exit Sub
    End If
    
    Dim rowCount, count, i As Long
    Dim colno As Integer
    
    search.lb_item.Clear
    
    colno = CInt(search.Range("E11").Value)
    rowCount = data.Range(search.Range("D11").Value & Rows.count).End(xlUp).Row
    count = 0
    
    For i = 2 To rowCount
        If InStr(1, Left(CStr(data.Cells(i, colno).Value), Len(txt_item.Text)), txt_item.Text, 1) Then
            search.lb_item.AddItem data.Cells(i, colno).Value
            count = count + 1
        End If
    Next i
    
    search.Range("F2").Value = count
    

End Sub

However to use this type of search for a data source exceeding 1 million records optimization is required. Indexing before search would be best in that case. Will update the same in the next blog.

Example File Link