Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to make database search return CONTAINS results instead of EXACT MATCH

0

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

Answer
Discuss

Discussion

Ompheta

Firstly, there's no need to capitalise words in your question title- we can read! (Also we can get annoyed, especially if a subject is all caps!)

Secondly, you say it's a macro from two years ago but in fact it's been modfied today by the answer I gave (on making the search case-insensitive). I'll answer separately...
John_Ru (rep: 6102) Jun 16, '21 at 3:12 pm
Add to Discussion

Answers

0
Selected Answer

Ompheta

Yes it's possible, by changing the IF test and using a different VBA function.

We can use InStr function which checks if a substring is In a String. If not, it returns a zero; if so, it returns a Long number (indicating the character poisition in the string where the substring starts).

For example, to do a case-sensitive find for the substring "Insur" in "Bailey Insurance", we'd use 

Instr("Bailey Insurance", "Insur")
which would return the value 8 (since Insur starts at the 8th position). We'd get 0 if the substring didn't match the capitalisation (e.g. substring="insur") but...

Although InStr is case-sensitive by default, we can make it case-insensitive by adding a 1 at the end (to specify a textual comparison) but we also need to add a 1 at the start too (for the starting character of the search), so changes in bold are:

Instr(1,"Bailey Insurance", "insur", 1
and we get 8 again.

To get the macro to search in this way, change this line (here showing a case-insenitive comparison):

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

If Instr(1, dataArray(i, searchField), searchValue, 1)>0 Then
Here's the Microsoft guidance on InStr function

Hope this does what you want.

Discuss

Discussion

Ompheta

Thanks for selecting my Answer (I guess that my suggestion works).
John_Ru (rep: 6102) Jun 16, '21 at 4:21 pm
Add to Discussion


Answer the Question

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