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.