expanding VBA fieldvalue to include all columns

Good day,

After going through the video

Fast Search Entire Excel Workbook & Return All Results into a Dashboard

(Excellent by the way), and playing with the code for a bit, it will work quite well for my needs except, in the user input, I don't have the capability of identifying fields/columns.

My data, spread out across several tabs, includes much of the same info, but organized differently depending on the worksheet.

Ex: Using your test data, the ID column in one sheet might be in the 8th column, and the 12th column in another sheet.

In this case, how would I approach this section of the code:

'Figure out by which field we will search.
If (fieldValue = "ID") Then
    searchField = 1
ElseIf (fieldValue = "Name") Then
    searchField = 2
End If

Are their special characters in VBA  that represent wildcard? Like we could say "searchfield = *", and it will look for fieldValue = "ID" across all columns of all the sheets rather than just looking up the searchField = 1 column of each sheet in the workbook?

*Edit

It didn't seem to work, got a syntax error, wondering though if I tinkered with the code too much and might just need to pull it back. This is the current state of the whole thing:

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("Dashboard")



'Data table information

dataColumnStart = 1

dataColumnEnd = 16

dataColumnWidth = dataColumnEnd - dataColumnStart ' Number of columns for the raw data (+1 not included because it makes lower calculations more confusing)

dataRowStart = 2

dashboardDataColumnStart = 7 ' Column for the data on the dashboard



'Get user input

searchValue = dashboard.Range("a2").Value

fieldValue = dashboard.Range("b2").Value



'Clear Dashboard

Call Clear_Data



'Loop through the worksheets

For Each ws In Worksheets



Select All

set searchFieldRange = ws.Range(1:1).Find(fieldValue)

searchField = searchFieldRange.Column



'Figure out by which field we will search.

If (fieldValue = "ID") Then

    searchField = 1

ElseIf (fieldValue = "Name") Then

    searchField = 2

End If





    'Ignore the Dashboard worksheet

    If (ws.Name <> "Dashboard") 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("Dashboard")



'Data table information

dashboardDataColumnStart = 7 ' Column for the data on the dashboard

dashboardDataRowStart = 2



dashboard.Range(dashboard.Cells(dashboardDataRowStart, dashboardDataColumnStart), dashboard.Cells(Rows.Count, Columns.Count)).Clear



End Sub

'Get user input

searchValue = dashboard.Range("a2").Value

fieldValue = dashboard.Range("b2").Value