Excel工具库存VBA搜索功能优化：实现模糊匹配需求

Got it, let's adjust your VBA code to enable fuzzy (partial match) search instead of exact matches. The key issue with your current code is the strict equality check—we'll swap that out for logic that detects if your search term appears anywhere in the tool name.

Modified VBA Code #

Option Compare Text
Sub searchable()
    Dim erow As Long
    Dim ws As Worksheet
    Dim lastrow As Long
    Dim count As Integer
    Dim searchTerm As String
    
    ' Capture and clean up the search term
    searchTerm = Trim(Sheet2.Range("B3").Value)
    
    lastrow = Sheets("ESOS").Cells(Rows.count, 1).End(xlUp).Row
    Sheet2.Range("a11:F6000").ClearContents
    count = 0
    Dim p As Long
    p = 11
    
    For x = 2 To lastrow
        ' Use InStr to check if the search term exists within the tool name
        ' Option Compare Text makes this case-insensitive automatically
        If InStr(Sheets("ESOS").Cells(x, 1).Value, searchTerm) > 0 Then
            ' Copy matching row data to Sheet2
            Sheets("ESOS").Rows(x).Columns("A:F").Copy Sheet2.Cells(p, 1)
            p = p + 1
            count = count + 1
        End If
    Next x
    
    ' Handle empty search input for better user experience
    If searchTerm = "" Then
        MsgBox "Please enter a search term in cell B3."
    Else
        MsgBox "The number of data found for this item code is " & count
    End If
End Sub

Key Changes Explained #

• Fuzzy Match Logic: Replaced the exact equality check = Sheet2.Range("B3") with InStr(Sheets("ESOS").Cells(x, 1).Value, searchTerm) > 0. The InStr function returns the position of your search term within the tool name—any value greater than 0 means the term is present (partial match).
• Case Insensitivity: Your existing Option Compare Text at the top ensures searches are case-insensitive, so typing dri, Drill, or DRILL will all find Drill bits.
• Cleaner Row Copy: Swapped the 6 individual column copy lines for a single line that copies the entire A:F range from the matching row. This makes the code easier to maintain if you add more columns later.
• Empty Search Handling: Added a check for blank input to show a helpful message instead of returning 0 results.
• Trimmed Search Term: Used Trim() to remove extra spaces from the search input, so accidental leading/trailing spaces won't break your search.

内容的提问来源于stack exchange，提问作者Ryan Nguyen