如何修改VBA代码实现基于Excel C列的列表框搜索功能？

Got it, let's tweak this code for you! The core change here is shifting the search target from Column B to Column C. I'll walk you through the adjusted code and highlight the key modifications so you can easily swap it in:

Private Sub TextBox1_Change()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim searchText As String
    
    ' Clear the list box first to avoid duplicate entries
    ListBox1.Clear
    
    ' Grab the search input and convert to lowercase for case-insensitive matching (optional but handy)
    searchText = LCase(TextBox1.Value)
    
    ' Define the target worksheet
    Set ws = ThisWorkbook.Sheets("Sheet2")
    
    ' Find the last row with data in Column C (instead of B)
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    
    ' Loop through Column C to find matches
    For i = 2 To lastRow ' Assumes row 1 is your header row; adjust if needed
        ' KEY CHANGE: Switched from Column B (Cells(i, 2)) to Column C (Cells(i, 3))
        If LCase(ws.Cells(i, 3).Value) Like "*" & searchText & "*" Then
            ' Add matching row data to the list box - adjust these columns to fit your needs
            ListBox1.AddItem ws.Cells(i, 1).Value ' Column A content
            ListBox1.List(ListBox1.ListCount - 1, 1) = ws.Cells(i, 2).Value ' Column B content
            ListBox1.List(ListBox1.ListCount - 1, 2) = ws.Cells(i, 3).Value ' Column C (HSN) content
            ' Add more columns here if you want to display additional data
        End If
    Next i
    
    ' Clean up the worksheet object
    Set ws = Nothing
End Sub

Key Modifications Breakdown: #

• Target Column Swap: Changed all references from Column B (index 2 or range "B") to Column C (index 3 or range "C"). This ensures the search logic checks your HSN values instead of the old B column data.
• Last Row Calculation: Updated the last row detection to use Column C, so we only loop through valid HSN entries instead of unnecessary rows.
• If your original code used Range("B:B") for the search range, make sure to replace that with Range("C:C") too.

If you want exact matches instead of fuzzy (partial) matches, just replace the Like "*" & searchText & "*" condition with = searchText. But since you mentioned inputting HSN1, HSN2 etc., the fuzzy match should work better for partial hits.

内容的提问来源于stack exchange，提问作者jomen40544jmail7.com