I used the VBA macro from 2 years ago to create a search box that looks through data on a seperate worksheet and displays results corresponding to an inputted search value. It's working great, except for the fact that my search input has to match exactly what is in the data table. I would like this to return results containing the search parameter. For example, I have a data value as "Bailey Insurance". I would like to be able to search "Bailey" and have the dashboard return the row for "Bailey Insurance". Is this possible?
Sub Data_Search()
' TeachExcel.com
Dim ws As Worksheet
Dim dashboard As Worksheet
Dim dataArray As Variant
Dim datatoShowArray As Variant
'Application.ScreenUpdating = False 'Turning off for the macro can speed things up - not so much here though.
'Dashboard sheet
Set dashboard = Sheets("LookUp")
'Data table information
dataColumnStart = 1
dataColumnEnd = 11
dataColumnWidth = dataColumnEnd - dataColumnStart ' Number of columns for the raw data (+1 not included because it makes lower calculations more confusing)
dataRowStart = 2
dashboardDataColumnStart = 2 ' Column for the data on the dashboard
'Get user input
searchValue = dashboard.Range("D8").Value
FieldValue = dashboard.Range("F8").Value
'Clear Dashboard
Call Clear_Data
'Figure out by which field we will search.
If (FieldValue = "Last Name") Then
searchField = 1
ElseIf (FieldValue = "First Name") Then
searchField = 2
ElseIf (FieldValue = "Business Name") Then
searchField = 3
End If
'Loop through the worksheets
For Each ws In Worksheets
'Ignore the Dashboard worksheet
If (ws.Name <> "LookUp") 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 LCase(dataArray(i, searchField)) = LCase(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 i
'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
End If
'Go to the next worksheet.
Next ws
'Application.ScreenUpdating = True 'Turn it back on at the end of the macro!
End Sub
Sub Clear_Data()
'Dashboard sheet
Set dashboard = Sheets("LookUp")
'Data table information
dashboardDataColumnStart = 2 ' Column for the data on the dashboard
dashboardDataRowStart = 20
dashboard.Range(dashboard.Cells(dashboardDataRowStart, dashboardDataColumnStart), dashboard.Cells(Rows.Count, Columns.Count)).Clear
End Sub