Selected Answer
Squishy.
Seems to me that your loops are wrong (would miss out rows) and your data array referencing is incorrect in places too (you got the dimensions swapped).
In the revised file attached:
- data validation has been removed for Dashboard cell F4 (for my test purposes)
- the code now seeks that value (e.g. Signal Name) in the first row of any searched file then checks in that column for matches with the value set in F6.
The revised code is below (with comments and changes in bold):
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 = 43
dataColumnWidth = dataColumnEnd - dataColumnStart
dataRowStart = 1
dashboardDataColumnStart = 9
searchValue = dashboard.Range("F6").Value
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) + 2, 1 To UBound(dataArray, 2))
j = 1
' loop through headers and give file details in dashboard
For k = 1 To UBound(dataArray, 2)
datatoShowArray(1, k) = ""
datatoShowArray(1, 1) = "From file: " & wbSlave.Path & "\" & wbSlave.Name & " - " & ws.Name
datatoShowArray(2, k) = dataArray(1, k)
Next k
' Loop again
For k = 1 To UBound(dataArray, 2)
' check that field name matches
If dataArray(1, k) = dashboard.Range("F4").Value Then
'if so, go down that column and find search value
For m = 1 To UBound(dataArray, 1)
If searchValue = dataArray(m, k) Then
For u = 1 To UBound(dataArray, 2)
datatoShowArray(j + 2, u) = dataArray(m, u)
Next u
j = j + 1
End If
Next m
End If
Next k
nextRow = dashboard.Cells(Rows.Count, dashboardDataColumnStart).End(xlUp).Row + 3
dashboard.Range(dashboard.Cells(nextRow, dashboardDataColumnStart), dashboard.Cells(nextRow + UBound(datatoShowArray, 1) - 1, dashboardDataColumnStart + dataColumnWidth)).Value = datatoShowArray
End If
Next ws
End Sub
Hope this works well for you.