VBA search multiple sheets with partial match using array

Nate3406

New Member
Joined
Feb 19, 2020
Messages
2
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,514
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Try changing this
VBA Code:
If (dataArray(i, searchField) = searchValue) Then
to
VBA Code:
If InStr(1, dataArray(i, searchField), searchValue, vbTextCompare) > 0 Then
 

Nate3406

New Member
Joined
Feb 19, 2020
Messages
2
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi & welcome to MrExcel.
Try changing this
VBA Code:
If (dataArray(i, searchField) = searchValue) Then
to
VBA Code:
If InStr(1, dataArray(i, searchField), searchValue, vbTextCompare) > 0 Then
That works perfect! thank you so much! for my own understanding on this, by changing it to Text Compare does that change it from searching for the exact value to similar characters within the array search field? Just looking for some context so I understand it for future use. Again thank you.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,514
Office Version
  1. 365
Platform
  1. Windows
InStr looks for an exact match for the search value within the string, but is not case sensitive.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,504
Messages
5,625,186
Members
416,077
Latest member
SJSB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top