Selected Answer
I went through your code, commented it and made some corrections. Here is the present status. Please take note of my remarks.
Option Explicit ' ALWAYS use Option Explicit
' Tools > Options > Editor:-
' Check "Require variable declarations" to set the option automatically
Sub Data_Search()
' always indent the entire content of procedures so that
' you can always see their beginning and end clearly
Dim Search As Worksheet
Dim Dashboard As Worksheet
Dim Ws As Worksheet
Dim Tbl As ListObject ' this is a worksheet table
Dim Rng As Range
Dim dataArray As Variant
Dim dataToShowArray As Variant
Dim dataColumnStart As Long
Dim dataColumnEnd As Long
Dim dataColumnWidth As Long ' misleading: suggest dataColumnCount
Dim dataRowStart As Long
Dim SearchDataColumnStart As Long
Dim SearchDataRowStart As Long
Dim searchValue As Variant
Dim searchField As Integer
Dim FieldValue As Variant
Dim R As Long ' R for "Row"
Dim C As Long ' C for "Column"
Dim i As Long ' i for "index"
Dim NextRow As Long
Application.ScreenUpdating = False 'Turning off for the macro can speed things up - not so much here though.
' illogical: "Search" is declares as "Worksheet", not as "Sheet"
' "Sheets" is a bigger collection incl sheets that aren't worksheets
Set Search = Sheets("Search") ' = Worksheets("Search")
Set Tbl = Worksheets("Blank").ListObjects(1)
dataColumnStart = 1
dataColumnEnd = 15
dataColumnEnd = Tbl.ListColumns.Count
' dataColumnWidth = dataColumnEnd - 1
dataColumnWidth = dataColumnEnd - dataColumnStart ' probably illogical
dataRowStart = 2
dataRowStart = Tbl.DataBodyRange.Row
' all of the above appears unnecessary.
' Consider referring to Tbl.DataBodyRange which starts in row 2 of Tbl.Range
' Consider the use of named ranges in your worksheet.
' Syntax, like, Range("SearchCriterium").Value
searchValue = Search.Range("D5").Value
FieldValue = Trim(Search.Range("F5").Value)
SearchDataColumnStart = 2 ' Column for the data on the Search
SearchDataRowStart = 17
'Figure out by which field we will search.
' If (FieldValue = "Name") Then
' searchField = 2
' ElseIf (FieldValue = "Adress") Then
' searchField = 3
' ElseIf (FieldValue = "Phone") Then
' searchField = 5
' ElseIf (FieldValue = "Invoice#") Then
' searchField = 7
' End If
Select Case FieldValue
Case "Name"
searchField = 2
Case "Adress"
searchField = 3
Case "Phone"
searchField = 5
Case "Invoice#"
searchField = 7
Case Else
MsgBox "You have selected an unlisted field name.", _
vbInformation, "Invalid search criteria"
Exit Sub
End Select
'Loop through the worksheets [in ThisWorkbook - by default]
For Each Ws In Worksheets
'Ignore the Dashboard worksheet
If (Ws.Name <> "Search" And Ws.Name <> "Blank") Then
With Ws
Set Rng = .Range(.Cells(dataRowStart, dataColumnStart), _
.Cells(.Rows.Count, dataColumnStart).End(xlUp)) _
.Resize(, dataColumnEnd)
End With
dataArray = Rng.Value
' consider this alternative:
dataArray = Ws.ListObjects(1).DataBodyRange.Value
ReDim dataToShowArray(1 To UBound(dataArray, 1), 1 To UBound(dataArray, 2))
' ' Row increment for the final data array (datatoShowArray).
' i = 1
' if i is reset here it can't continue counting entries
' extracted from the next worksheet. Entry #1 gets over-written.
' I prefer i to be a natural zero at the beginning of the proc.
' and increment only after something has been found.
' In this way i reflects the actual number of entries matched.
'Loop through the rows in the data range.
For R = 1 To UBound(dataArray, 1)
'Check if the value in the row equals our search value
If (dataArray(R, searchField) = searchValue) Then
'MATCH FOUND! Now do something!
i = i + 1 ' Increment the counter for the datatoShowArray
'Loop through the columns in the data range so can get their values.
For C = 1 To UBound(dataArray, 2)
'Add values to the array that will be used to put data into the Dashboard.
dataToShowArray(i, C) = dataArray(R, C)
Next C
' must you continue looking at all names after a match has
' been found in a Ws?
' Exit For ' search has been successful
End If
Next R
End If
Next Ws
If i Then
'Clear Dashboard
Clear_Data Search, SearchDataColumnStart ' pass arguments
'Find next empty row in the Search.
NextRow = Search.Cells(Search.Rows.Count, SearchDataColumnStart).End(xlUp).Row + 1
' you have cleared Search. Therefore the row is a constant (declared as variable here)
' The variable NextRow isn't required at all.
NextRow = SearchDataRowStart
'Put data into the dashboard.
'Format = Range(Cells(1,1),Cells(2,2)).Value = datatoShowArray
With Search
Set Rng = .Range(.Cells(NextRow, SearchDataColumnStart), _
.Cells(NextRow + UBound(dataToShowArray, 1) - 1, SearchDataColumnStart + dataColumnWidth))
' consider this :
Set Rng = .Cells(NextRow, SearchDataColumnStart).Resize(UBound(dataToShowArray, 1), _
UBound(dataToShowArray, 2))
' still easier with Tbl.DataBodyRange
End With
' Search.Range(Cells(NextRow, SearchDataColumnStart), Cells(NextRow + UBound(dataToShowArray, 1) - 1, _
' SearchDataColumnStart + dataColumnWidth)).Value = dataToShowArray
Rng.Value = dataToShowArray
End If
Application.ScreenUpdating = True 'Turn it back on at the end of the macro!
MsgBox IIf(i, i, "No") & " matching record" & IIf(i > 1, "s were", " was") & " found.", _
vbInformation, "Search result"
End Sub
Private Sub Clear_Data(Ws As Worksheet, ByVal SearchDataColumnStart As Long)
'Search sheet
Dim SearchDataRowStart As Long
'Data table information
SearchDataRowStart = 17
' ClearContents leaves the formatting in place
With Ws
' both Rows and Columns must be counted in the same worksheet
' hence the leading periods here which you have omitted.
' Note: Columns.Count = > 16000. Consider Ws.UsedRange.Columns.Count
.Range(.Cells(SearchDataRowStart, SearchDataColumnStart), .Cells(.Rows.Count, .Columns.Count)).Clear
End With
' consider: having a table in that sheet
' Ws.ListObjects(1).DataBodyRange.ClearContents
End Sub
To tell the truth, I corrected your code until it couldn't work anymore - and then I gave up :-). I found that I don't know what you want. Start with my remark at the end of the first inside loop that you might not need to continue searching after a match has been found. That is definitely so when you search for an Invoice# because invoice numbers should be unique but I don't know if you would want to list recurring Names. Your code can't do that and when I tried to make mine do it I ran into complications which I didn't want to deal with without knowing your intention - and then maybe not, either. It would appear that the exit from the loop must be dependent upon the search field.
For now, the DataToShowArray is re-dimensioned for every worksheet. That doesn't match the index variable (i) that counts results through all worksheets the way I made it. But if you want to collect results from multiple worksheets the dimension of DataToShowArrayis wrong. The last dimension (UBound, 2) should be an arbitrary high number and UBound,2 be reset to the actual number of records as counted before pasting the result. But this can only be done for the last vector, meaning you would have to switch vectors when you transfer data from dataArray, and then use Rng.Value = Application.Transpose(dataToShowArray) in the end.
It's a lot of work - and this wasn't even your question. Your question has a solution that is unrelated to VBA - quite funny actually after all the effort that went into checking the code, but I needed to understand the code to find the problem. The problem is caused by two spelling errors. "Invoice#" is misspelled in the drop-down list and "Address" is misspelled in the code. I have added a MsgBox to the code which would alert you to such irregularities.