I have this code that I tweaked in our department, and it works.

But the problem is that the database has items(rows) with spaces at the end of the "name" cell. For example, whenever I search the "name" column for "chris", All "chris" will be display but not those with a space("chris ") at the end.

Is there a method to include all the matches both with and without space? The database is protected so I cannot edit it and remove all the spaces.

Sub Data_Search(wbMaster As Workbook, wbSlave As Workbook)

Dim ws As Worksheet
Dim dashboard As Worksheet
Dim dataArray As Variant
Dim datatoShowArray As Variant

Set dashboard = wbMaster.Sheets("Dashboard")


dataColumnStart = 1
dataColumnEnd = 15
dataColumnWidth = dataColumnEnd - dataColumnStart
dataRowStart = 2
dashboardDataColumnStart = 2

searchValue = dashboard.Range("C4").Value
fieldValue = dashboard.Range("E4").Value


If (fieldValue = "ID") Then
    searchField = 1
ElseIf (fieldValue = "Name") Then
    searchField = 2
End If


For Each ws In wbSlave.Worksheets


    If (ws.Name <> "Dashboard") Then


        dataArray = ws.Range(ws.Cells(dataRowStart, dataColumnStart), ws.Cells(ws.Cells(Rows.Count, dataColumnStart).End(xlUp).Row, dataColumnEnd)).Value

        ReDim datatoShowArray(1 To UBound(dataArray, 1), 1 To UBound(dataArray, 2))

        j = 1

        For i = 1 To UBound(dataArray, 1)

            If (dataArray(i, searchField) = searchValue) Then


                For k = 1 To UBound(dataArray, 2)

                    datatoShowArray(j, k) = dataArray(i, k)

                Next k


                 j = j + 1

            End If

        Next i


        nextRow = dashboard.Cells(Rows.Count, dashboardDataColumnStart).End(xlUp).Row + 1

        dashboard.Range(dashboard.Cells(nextRow, dashboardDataColumnStart), dashboard.Cells(nextRow + UBound(datatoShowArray, 1) - 1, dashboardDataColumnStart + dataColumnWidth)).Value = datatoShowArray

    End If

1 Answers

0
Kelvin Lai On Best Solutions

You can use the Trim() function to create a copy of the item with any whitespace before and after it removed.

In your case, change this line

If (dataArray(i, searchField) = searchValue) Then

to

If (Trim(dataArray(i, searchField)) = searchValue) Then

Reference:
https://docs.microsoft.com/en-us/office/vba/Language/Reference/User-Interface-Help/ltrim-rtrim-and-trim-functions