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 case INSENSITIVE results

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 is case sensitive. For example, I have a value in my table of "ALLEN". If I search "ALLEN", Excel returns the results perfectly. But if I search "Allen", no results are returned, because the case does not match. I am importing data from several different worsheets with thousands of rows, it would not be feasable for me to alter the case of every entry and then hope that everyone in my office searches with the same case. Is there a way to have excel search for the letters "allen" regardless of the case?

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 = 10
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
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 (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!

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

Answers

0
Selected Answer

Ompheta

You can make the search case-insensitive by converting each side of the IF test of values to lowercase. Just change this line (with the additions in bold) :

'Check if the value in the row equals our search value but compare lowercase values
            If Lcase(dataArray(i, searchField)) = Lcase(searchValue) Then  
That should do it. 
Discuss


Answer the Question

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