Search table using partial text and return all matches

[Smithy2k7]

Hello, I have a table which when completed will be used as a point of reference to find part numbers etc. I am looking to set up a search box which when a partial search string such 'BATT' is entered, it will return all matches in the table which contains 'BATT'.

Column A is stock number, column B is Type and column C is description. When part descriptions are entered I would like it to return all matches using the description, but to also to return the stock number of the matched items.

Thanks.

 

[ParamRay]

.
.

You could use conditional formatting, which, in turn, will enable you to filter by cell colour.


(1) Highlight column C (containing your descriptions);

(2) Create a conditional formatting rule; and

Home --> Styles --> Conditional Formatting --> Highlight Cells Rules --> Text that Contains...

(3) In the input box, enter the address of the cell in which users enter the text.

 

[Smithy2k7]

This database is ultimately going to contain hundreds of entries, possibly thousands so although that way would work I dont want the end user to have filter by colour etc. My idea and hope, is that the user types in a criteria, lets say 'Bat' because he/she doesnt know the exact name of what they want. I'd like it to return in separate rows, all entrys containing 'Bat' along with the stock number in column A.

Thanks

 

[ParamRay]

.
.

Perhaps you can adapt something like the following.

(Note that the code should be placed in the code module for that particular worksheet.)

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Crit As Range
    Dim FCnt As Integer
    Dim i As Integer
    
    'set cell for search criteria
    Set Crit = Me.Range("J1")
    
    If Target.Address = Crit.Address Then
        Application.ScreenUpdating = False
        
        'clear existing filter
        On Error Resume Next
        Me.ShowAllData
        On Error GoTo 0
    
        With Me.Range("A1").CurrentRegion
            
            'add filter with hidden dropdown
            FCnt = .Columns.Count
            For i = 1 To FCnt
                .AutoFilter Field:=i, VisibleDropdown:=False
            Next i
            
            'filter by search criteria
            .AutoFilter Field:=3, Criteria1:="=*" & Crit.Value & "*"
        End With
            
        Application.ScreenUpdating = True
        Crit.Select
    End If
End Sub