Hello, I am relatively new and am just diving back into VBA after several years. I am running into some trouble with trying to search for partial matches with a search function that uses arrays. I am using code from Teach Excel and will not take credit for the coding. I have made small modifications to fit my needs. I am wanting the search function to be able to search for partial matches and not be case sensitive. I am not sure at what point in the coding I need to make a modification, I believe it is where I set the search value but am unclear as to how that is done. I am sure I am overthinking it but have been stumped for a while now. anything advice would be helpful.
VBA Code:
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.
'StandardHrsInq sheet
Set dashboard = Sheets("StandardHrsInq")
On Error GoTo ErrorHandler
'Data table information
dataColumnStart = 1
dataColumnEnd = 14
dataColumnWidth = dataColumnEnd - dataColumnStart ' Number of columns for the raw data (+1 not included because it makes lower calculations more confusing)
dataRowStart = 3
dashboardDataColumnStart = 2 ' Column for the data on the dashboard
dashboarDataRowStar = 13
'Get user input
'If dashboard.Range("N4") = "" Then
'GoTo ErrorHandler
'End If
'If dashboard.Range("O4") = "" Then
'GoTo ErrorHandler
'End If
If dashboard.Range("M4") = "PSQ#" Then
searchValue = dashboard.Range("O4").Value
Else
searchValue = dashboard.Range("N4").Value
End If
fieldValue = dashboard.Range("M4").Value
'Clear Dashboard
Call Clear_Data
'Figure out by which field we will search.
If (fieldValue = "Arrangement Number") Then
searchField = 2
ElseIf (fieldValue = "Component Description") Then
searchField = 1
ElseIf (fieldValue = "Repair Option") Then
searchField = 11
ElseIf (fieldValue = "PSQ#") Then
searchField = 12
ElseIf (fieldValue = "Backup Parts List") Then
searchField = 14
End If
'Loop through the worksheets
For Each ws In Worksheets
'Ignore the Dashboard worksheet
If (ws.Name <> "StandardHrsInq" And ws.Name <> "Master") 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 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!
ErrorHandler:
If dashboard.Range("N4") = "" Then
MsgBox "please enter value"
End If
End Sub
Sub Clear_Data()
'Dashboard sheet
Set dashboard = Sheets("StandardHrsInq")
'Data table information
dashboardDataColumnStart = 2 ' Column for the data on the dashboard
dashboardDataRowStart = 13
dashboard.Range(dashboard.Cells(dashboardDataRowStart, dashboardDataColumnStart), dashboard.Cells(Rows.Count, Columns.Count)).Clear
End Sub
