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

0
Good day, I found this video the Fast Search Entire Excel Workbook & Return All Results into a Dashboard on youtube and the code has been a great help BUT. 

I need some help as I want to expand the searched area from 1 colum to 8. so far i have  this code as seen below

Sub Data_Search()
' TeachExcel.com

Dim ws As Worksheet
Dim Search 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.

'Search sheet
Set Search = Sheets("Search")

'Data table information
dataColumnStart = 1
dataColumnEnd = 12
dataColumnWidth = dataColumnEnd - dataColumnStart ' Number of columns for the raw data (+1 not included because it makes lower calculations more confusing)
dataRowStart = 2
SearchDataColumnStart = 2 ' Column for the data on the Search

'Get user input
searchValue = Search.Range("C4").Value
fieldValue = Search.Range("E4").Value

'Clear Search
Call Clear_Data

'Figure out by which field we will search.
If (fieldValue = "Staff Member") Then
    searchField = 1
ElseIf (fieldValue = "Key") Then
    searchField = 3
End If

'Loop through the worksheets
For Each ws In Worksheets

    'Ignore the Search worksheet
    If (ws.Name <> "Search") 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 Search.
                    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 Search.
        nextRow = Search.Cells(Rows.Count, SearchDataColumnStart).End(xlUp).Row + 1

        'Put data into the Search.
        'Format = Range(Cells(1,1),Cells(2,2)).Value = datatoShowArray
        Search.Range(Cells(nextRow, SearchDataColumnStart), Cells(nextRow + UBound(datatoShowArray, 1) - 1, SearchDataColumnStart + 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!
'https://www.youtube.com/watch?v=DOW3SjBjg6I
End Sub

Sub Clear_Data()

'Search sheet
Set Search = Sheets("Search")

'Data table information
SearchDataColumnStart = 2 ' Column for the data on the Search
SearchDataRowStart = 11

Search.Range(Search.Cells(SearchDataRowStart, SearchDataColumnStart), Search.Cells(Rows.Count, Columns.Count)).Clear

End Sub

in the search secion it goes

If (fieldValue = "Staff Member") Then
    searchField = 1
ElseIf (fieldValue = "Key") Then
    searchField = 3
End If

How can i change the second option to search 3 through 11? 

as I am trying to have a simple form to search 8 colunms across 7 sheets....

Is there a comand that replaces elseif to be continue searching... then end after the last one with the else if statement?

Thanks.

Answer
Discuss

Answers

0

Look at Find and FindNext. It's all described here.

Discuss

Discussion

I apreciate the info!  Still super new to excel, can this be easily integrated itno the code above and still retain the original fucnion? sorry like i said i dont code often been 20 years....
Caboose Apr 15, '21 at 8:50 pm
No. The verdict is that your code supplier tried to re-invent the wheel and didn't figure a way yet to find the center. The task appears to be similar to what you get which you click Edit > Find > Find All but with a flourish. If you would explain what you are trying to find - I mean, explain what you want as opposed to what the code does - I might be able to help you out. 
Please don't add the explanation in the discussion here but add it to your question. You can still edit it.
Variatus (rep: 4549) Apr 15, '21 at 9:33 pm
Add to Discussion


Answer the Question

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