String within string



 I watched your video on how to "Fast Search Entire Excel Workbook" and it has been extremely instrumental in helping me create a search engine. (Video: )

That being said, the code in the video only accounts for exact matches of the keywords. Is there a way for the search engine to spit out instances of searches? For example, if I would like to search the word "brown" in a field called "description" where the description is "the dog is brown with some white freckles", is there a way for the program to give me the whole cell result after matching just one instance of the keyword in the string? 

The existing code for the search is as below: 

'Loop through the worksheets

For Each ws In wbSlave.Worksheets

    'Ignore the Dashboard worksheet

    If (ws.Name <> "Dashboard") Then

        'Get the range values into a variable that can be looped through.

        'Example usage: dataArray(1,1) [row,column]

        'Simple version: ws.Range(Cells(1,1),Cells(2,2)).Value

        dataArray = ws.Range(ws.Cells(dataRowStart, dataColumnStart), ws.Cells(ws.Cells(Rows.Count, dataColumnStart).End(xlUp).Row, dataColumnEnd)).Value

        'Increase size of array that will hold the data to display to its max possible size for the current worksheet.

        ReDim datatoShowArray(1 To UBound(dataArray, 1), 1 To UBound(dataArray, 2))

        'Row increment for the final data array (datatoShowArray).

        j = 1

        'Loop through the rows in the data range.

        For i = 1 To UBound(dataArray, 1)

            'Check if the value in the row equals our search value

            If (dataArray(i, searchField) = searchValue) Then

                'MATCH FOUND! Now do something!

                'Loop through the columns in the data range so can get their values.

                For k = 1 To UBound(dataArray, 2)

                    'Add values to the array that will be used to put data into the Dashboard.

                    datatoShowArray(j, k) = dataArray(i, k)

                Next k

                'Increment the counter for the datatoShowArray

                j = j + 1

            End If


        'Find next empty row in the dashboard.

        nextRow = dashboard.Cells(Rows.Count, dashboardDataColumnStart).End(xlUp).Row + 1

        'Put data into the dashboard.

        'Format = Range(Cells(1,1),Cells(2,2)).Value = datatoShowArray

        'dashboard.Range(Cells(nextRow, dashboardDataColumnStart), Cells(nextRow + UBound(datatoShowArray, 1) - 1, dashboardDataColumnStart + dataColumnWidth)).Value = datatoShowArray

        dashboard.Range(dashboard.Cells(nextRow, dashboardDataColumnStart), dashboard.Cells(nextRow + UBound(datatoShowArray, 1) - 1, dashboardDataColumnStart + dataColumnWidth)).Value = datatoShowArray

    End If

'Go to the next worksheet.

Next ws

'Application.ScreenUpdating = True 'Turn it back on at the end of the macro!


End Sub



Selected Answer

You can use the InStr funciton.

Try replacing this line:

If (dataArray(i, searchField) = searchValue) Then

with this:

If (InStr(dataArray(i, searchField),searchValue) > 0)  Then

I haven't tested this but I think it should work. Here is the MSDN page for it: InStr



Hi Don,  Thank you so much, it works!  Would there be any way for me to optimize the code to spit out results even for partial strings? For example if one searches up "kev" in the field 'name' - it should show all results for the name "Kevin Durant". I want the search to be as robust as possibe  Thanks again! 
rluth (rep: 2) Jul 8, '19 at 10:18 am
InStr should work for partial strings. Have you tried it?
don (rep: 1825) Jul 8, '19 at 9:15 pm
Yes, it works. When I posted this comment I didnt account for case sensitivity, which I have now fixed. Thanks for all your help! 
rluth (rep: 2) Jul 9, '19 at 12:11 pm
No worries, and you can just put UCase() or LCase() around both pieces of text so they are both upper case or both lower case, and that's an easy way to solve that.
don (rep: 1825) Jul 11, '19 at 6:38 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login