Search for (1), (2), (3) values in one cell

Hello,

I have a datebase of hardware, those hardware have description, one description per cell.

How can I create a macro with a user interface so the user can search for (1), (2), or (3) values in the description column? Then maybe highlight the row that matches the description.

Example:

Row 1: 18-8 Stainless Steel Socket Head Screw (Hex Drive), M8 x 1.25 mm Thread, 16 mm Long
Row 2: 18-8 Stainless Steel Socket Head Screw (Hex Drive), M2.5 x 0.45 mm Thread, 5 mm Long
Row 3: 18-8 Stainless Steel Flat Head Screw (Hex Drive), M5 x 0.8 mm Thread, 12 mm Long

I want the user to be able to search for [18-8] + [hex] + [M5], then Row 3 is highlighted.

Is this doable?

Thank you very much in advance for any help

Try something like this...

Private Sub CommandButton1_Click()

Dim lrow As Long
Dim i As Long

lrow = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row

For i = 1 To lrow
    Range("G" & i).Interior.Color = vbWhite
    If InStr(Range("G" & i).Value, Range("J2").Value) > 0 And _
        InStr(Range("G" & i).Value, Range("J3").Value) > 0 And _
        InStr(Range("G" & i).Value, Range("J4").Value) > 0 Then
        
            Range("G" & i).Interior.Color = vbRed
    End If
Next i
 
End Sub

Originally Posted by 123wc

... How can I create a macro with a user interface ... ?

simply,
two beer or not two beer,
one or more bars of chocolate,
hang a sign on the door to the room with the words "man at work"
... and to the work


For example:

1. Standard module

Option Explicit
Option Private Module

Sub search_for_3part_in_cell()
    Load UserForm1
    UserForm1.Show vbModeless
End Sub

2. UserForm module

Option Explicit

'Images data
Const imgfldr = "Images"
Const blnkimg = "Blank"
Const imgpref = "Part"
Const ext = ".jpg"

'Search schemes
Const sptrn1 = "##-#*(??? *), M# X*"    ' => M5/M8
Const sptrn2 = "##-#*(??? *), M#.# X*"  ' => M2.5

'Worksheet data
Const shnme = "Sheet1"  'Sheet name
Const bgc = "A"         'First column
Const bgr = 1           'First row
Const srng = "G"        'Search column
Const bgsr = 2          'The first row in the search column

'UserForm data
Const cptn1 = "3 partial search " & " - User: " 'UserForm header

'Array data -> the size of the second index
Const rpa = 2

Private i As Long, ii As Long, iH As Long, iL As Long, ofst As Long
Private pttrn As String, strPath As String, strpth As String
Private dcsn As Boolean, skipthis As Boolean
Private cRng As Range
Private arr As Variant

Private Sub UserForm_Initialize()
    skipthis = True
    pttrn = vbNullString
    
    With ThisWorkbook
        With .Sheets(shnme)
            ii = .Cells(.Rows.Count, bgc).End(xlUp).Row
            If ii = 1 Then End
            arr = .Range(srng & bgsr & ":" & srng & ii).Value
            Set cRng = .Range(bgc & bgr).CurrentRegion
        End With
        strPath = .Path & "\" & imgfldr & "\"
    End With
    
    iL = LBound(arr, 1)
    If iL = 0 Then ofst = 1 Else ofst = 0 'For 'Option Base 0/1'
    iH = UBound(arr, 1) + ofst
    ReDim Preserve arr(1 To iH, 1 To rpa)
    
    For i = iL To iH
        arr(i + ofst, rpa) = i + ofst 'For identify the row number
    Next
    
    With UserForm1
        .Caption = cptn1 & Environ("Username")
        
        .Label1.Caption = "Search string #1"
        .Label2.Caption = "Search string #2"
        .Label3.Caption = "Search string #3"
        .Label4.Caption = "Result"
        With .Label5
            .Caption = "No result"
            .Font.Size = 11
            .Font.Bold = True
            .ForeColor = vbBlue
        End With
        .Label6.Caption = "No image"
        
        .TextBox1.Value = vbNullString
        .TextBox2.Value = vbNullString
        .TextBox3.Value = vbNullString
        
        .CommandButton1.Caption = "Clear fields"
        .CommandButton2.Caption = "unused"
        .CommandButton3.Caption = "unused"
        .CommandButton4.Caption = "Close"
    End With
    
    skipthis = False
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If skipthis Then Exit Sub
    If CloseMode <> 1 Then Cancel = 1
End Sub

'--------------------------------------------------------------------------------------------------
Private Sub TextBox1_Change()
    If skipthis Then Exit Sub
    Call search_part(Me.TextBox1.Value, UCase(Me.TextBox2.Value), UCase(Me.TextBox3.Value))
End Sub
Private Sub TextBox2_Change()
    If skipthis Then Exit Sub
    Call search_part(Me.TextBox1.Value, UCase(Me.TextBox2.Value), UCase(Me.TextBox3.Value))
End Sub
Private Sub TextBox3_Change()
    If skipthis Then skipthis = False: Exit Sub
    Call search_part(Me.TextBox1.Value, UCase(Me.TextBox2.Value), UCase(Me.TextBox3.Value))
End Sub

Private Sub search_part(prt1 As String, prt2 As String, prt3 As String)
    dcsn = CBool(Len(prt1)) And CBool(Len(prt2)) And CBool(Len(prt3))
    
    If dcsn Then
        Me.Label5.Caption = "No result"
        Me.Label6.Caption = "No image"
        
        strpth = Dir(strPath & blnkimg & ext, vbNormal)
        If strpth <> "" Then Me.Label6.Picture = LoadPicture(strPath & strpth)
        
        cRng.Range(bgc & bgr).Select
        
        dcsn = False
        pttrn = prt1 & "*(" & prt2 & " *), " & prt3 & " X*"
        
        If pttrn Like sptrn1 Or pttrn Like sptrn2 Then
            For i = iL To iH
                If UCase(arr(i + ofst, 1)) Like pttrn Then
                    Me.Label5.Caption = arr(i + ofst, 1)
                    
                    strpth = Dir(strPath & imgpref & " " & i & ext, vbNormal)
                    If strpth <> "" Then Me.Label6.Picture = LoadPicture(strPath & strpth)
                    
                    pttrn = vbNullString
                    strpth = vbNullString
                    
                    cRng.Rows(i + bgr).Select
                    Exit For
                End If
            Next
        End If
    End If
End Sub
'--------------------------------------------------------------------------------------------------

Private Sub CommandButton1_Click()
    Me.TextBox1.Value = vbNullString
    Me.TextBox2.Value = vbNullString
    Me.TextBox3.Value = vbNullString
End Sub

Private Sub CommandButton2_Click()
    
End Sub

Private Sub CommandButton3_Click()
    
End Sub

Private Sub CommandButton4_Click()
    arr = Empty
    cRng.Range(bgc & bgr).Select
    Set cRng = Nothing
    skipthis = True
    
    UserForm1.Hide
    Unload UserForm1
End Sub

3. UserForm
Maybe like in the image and "xls" file below ... or maybe different

4. Pictures of "hardware" in appropriate resolution

5. In the archive there is a "database" file and an example directory with pictures

Originally Posted by 123wc

I want the user to be able to search for [18-8] + [hex] + [M5], then Row 3 is highlighted.

Simple use of CF.

Sub test()
    Dim myStr As String
    myStr = InputBox("Enter search string with asterisk", , "18-8*hex*M5*")
    With Range("g2", Range("g" & Rows.Count).End(xlUp))
        .FormatConditions.Delete
        .FormatConditions.Add 2, Formula1:="=isnumber(match(""" & myStr & """,g2,0))"
        .FormatConditions(1).Interior.Color = vbYellow
    End With
End Sub

WOW! You guys are great!!!

Here's the second part of it:

When I click search, say there are returned values Row 64 and Row 137 [both are highlighted]. Is there a way to keep the search portion floating so when I click search once it takes me to Row 64 and when I click search again it takes me to Row 137?

Also, is there a way to search for Hardware Type (Column B) and String 1 + String 2 + String 3?

Thank you very much! You guys are great!

Originally Posted by 123wc

Here's the second part of it:

And the third, forth, fifth...etc?

How can I make the macro look for a string regardless if the letters are caps or not?

Thanks in advance!

Private Sub CommandButton1_Click()

Dim lrow As Long
Dim i As Long

lrow = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row

For i = 1 To lrow
    Range("G" & i).Interior.Color = vbWhite
    If InStr(UCase(Range("G" & i).Value), UCase(Range("J2").Value)) > 0 And _
        InStr(UCase(Range("G" & i).Value), UCase(Range("J3").Value)) > 0 And _
        InStr(UCase(Range("G" & i).Value), UCase(Range("J4").Value)) > 0 Then
        
        Range("G" & i).Interior.Color = vbRed
    End If
Next i
 
End Sub