Search via Keyword and not String

Question

I am working on a project where I search an entire workbook and then have the results shown on a search page. So far I have it down to being able to search for a string and it finding that string. But if I just search for a keyword like "motor" it will show no results because no where in the workbook is just "motor" written in a cell. Is their any way that I can make it so that it will search for any thing that is typed in the search box?

Here is the code. The search script is Sub FindOne()

Private Sub ComboBox1_Change()

End Sub


Private Sub ComboBox2_Change()
    UpdateSearchBox
End Sub

Private Sub CommandButton1_Click()
    Select Case TextBox1.Value
        Case "F"
            TextBox1.Value = "G"
        Case "E"
            TextBox1.Value = "F"
        Case "D"
            TextBox1.Value = "E"
        Case "C"
            TextBox1.Value = "D"
        Case "B"
            TextBox1.Value = "C"
        Case "A"
            TextBox1.Value = "B"
        Case "G"
            TextBox1.Value = "A"
    End Select
End Sub

Private Sub CommandButton2_Click()
    FindOne
End Sub

Private Sub TextBox1_Change()
    UpdateSearchBox
End Sub

Sub UpdateSearchBox()
    Dim PageName As String, searchColumn As String, ListFiller As String
    Dim lastRow As Long

    If TextBox1.Value <> "" Then
        PageName = TextBox1.Value
    Else
        Exit Sub
    End If

    Select Case ComboBox2.Value
        Case "EQUIPMENT NUMBER"
            searchColumn = "A"
        Case "EQUIPMENT NAME"
            searchColumn = "C"
        Case "DUPONT NUMBER"
            searchColumn = "F"
        Case "SAP NUMBER"
            searchColumn = "G"
        Case "SSI NUMBER"
            searchColumn = "H"
        Case "PART NAME"
            searchColumn = "I"
        Case ""
            MsgBox "Please select a value for what you are searching by."
    End Select

    lastRow = Sheets(PageName).Range("A65536").End(xlUp).Row

        If lastRow <> 0 And PageName <> "" And searchColumn <> "" Then
        ListFiller = PageName & "!" & searchColumn & "2" & ":" & searchColumn & lastRow
        ComboBox1.ListFillRange = ListFiller
    End If
End Sub
Sub FindOne()

    Range("B19:J1500") = ""

    Application.ScreenUpdating = False

    Dim k As Integer, EndPasteLoopa As Integer
    Dim myText As String, searchColumn As String
    Dim totalValues As Long
    Dim nextCell As Range

    k = ThisWorkbook.Worksheets.Count
    myText = ComboBox1.Value
    Set nextCell = Range("B20")
    If myText = "" Then
        MsgBox "No Address Found"
        Exit Sub
    End If

    Select Case ComboBox2.Value
        Case "EQUIPMENT NUMBER"
            searchColumn = "A"
        Case "EQUIPMENT NAME"
            searchColumn = "C"
        Case "DUPONT NUMBER"
            searchColumn = "F"
        Case "SAP NUMBER"
            searchColumn = "G"
        Case "SSI NUMBER"
            searchColumn = "H"
        Case "PART NAME"
            searchColumn = "I"
        Case ""
            MsgBox "Please select a value for what you are searching by."
    End Select

    For i = 2 To k
        totalValues = Sheets(i).Range("A65536").End(xlUp).Row
        ReDim AddressArray(totalValues) As String

        For j = 0 To totalValues
            AddressArray(j) = Sheets(i).Range(searchColumn & j + 1).Value
        Next j

        For j = 0 To totalValues
            If (myText = AddressArray(j)) Then
                EndPasteLoop = 1
                If (Sheets(i).Range(searchColumn & j + 2).Value = "") Then EndPasteLoop = Sheets(i).Range(searchColumn & j + 1).End(xlDown).Row - j - 1
                For r = 1 To EndPasteLoop
                    Range(nextCell, nextCell.Offset(0, 8)).Value = Sheets(i).Range("A" & j + r, "I" & j + r).Value
                    Set nextCell = nextCell.Offset(1, 0)
                Next r
            End If
        Next j
    Next i

    Application.ScreenUpdating = True
End Sub

I have no idea if this is even possible. Thank You!

You can try `If instr(1, AddressArray(j), myText) > 0 Then`. But I see much room for improvement to your code. Consider posting it on https://codereview.stackexchange.com — A.S.H, Jun 27 '17 at 20:02
Hey, @CalebSutton I posted that as an answer a bit ago. If you mark it as correct, it will help people in the future find the right option for their code. Part of the community's job is to help everyone else. — Jsleshem, Jun 27 '17 at 20:34

score 0 · Answer 1 · answered Jun 27 '17 at 19:58

Use the Instr function. Here is an example of how it works:

Dim startPosition As Integer

startPosition = InStr("find the comma, in the string", ",")

This bit of code will return 15, saying that the position there there is a , is the 15th position.

Now just adjust this for your code. Iterate over what you want to search through. If the InStr function does not return 0, you've got a (semi)match.

If you want to learn more, go to this question: Check if a string contains another string.

score 0 · Answer 2 · answered Jun 27 '17 at 20:46

0

@A.S.H answered the question in the comments.

answered Jun 27 '17 at 20:46

Caleb Sutton

75
11

Search via Keyword and not String

2 Answers2