Hello,
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: https://www.youtube.com/watch?v=DOW3SjBjg6I )
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
Next
'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