I ran into " Fast Search Multiple Excel Workbooks using a File Picker Window" on youtube channel of teachexcel, and have since been trying to run it, only that I keep getting subscript out of range error at " If (dataArray(i, searchField) = searchValue) Then....
could I get some help in configuring out could have been the problem,
Sub Data_Search(wbMaster As Workbook, wbSlave As Workbook)
' 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 = wbMaster.Sheets("Dashboard")
'Data table information
dataColumnStart = 1
dataColumnEnd = 9
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("C2").Value
fieldValue = dashboard.Range("C4").Value
'Clear Dashboard
'Call Clear_Data()
'Figure out by which field we will search.
If (fieldValue = "CONTACT") Then
searchField = 3
ElseIf (fieldValue = "CONTACT POSTAL") Then
searchField = 9
End If
'Loop through the worksheets
For Each ws In wbSlave.Worksheets
'Ignore the Dashboard worksheet
If (ws.Name <> "Dashboard" And ws.Name <> "Sheet3" And ws.Name <> "WorkSheet1") 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
dashboard.Range(dashboard.Cells(nextRow, dashboardDataColumnStart), dashboard.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
Sub Search_Separate_Workbooks()
' TeachExcel.com
Dim wbMaster As Workbook
Dim wbSlave As Workbook
Dim filePicker As FileDialog
'Dont update the screen - makes it work faster
Application.ScreenUpdating = False
'Clear Dashboard
Call Clear_Data
'Set the master workbook - one that will display the results.
Set wbMaster = ActiveWorkbook
'Setup the file picker dialog box/window.
Set filePicker = Application.FileDialog(msoFileDialogFilePicker)
'Control the file picker.
With filePicker
.Filters.Clear
.Filters.Add "Excel Files", "*.xl*" ' Show only Excel files
.AllowMultiSelect = True
.Title = "My Title!"
End With
'Get the button that was pressed.
filePickerButtonClicked = filePicker.Show
'Check if use hit the Cancel button or not.
If filePickerButtonClicked <> 0 Then
'File(s) was selected, now do something!
'Loop through selected files.
For Each fileSelected In filePicker.SelectedItems
' Save a reference to, and open, the workbook to search through.
Set wbSlave = Workbooks.Open(fileSelected)
' Open the method that searches the workbooks and tell them which workbook to search and where to place the data.
Call Data_Search(wbMaster, wbSlave)
' Close the workbook that was searched through and don't save any changes to it, if any were made.
wbSlave.Close SaveChanges:=False
Next fileSelected
Else
'Cancel button clicked.
MsgBox "No File Selected!"
End If
'Turn on screen updating again - makes Excel usable
Application.ScreenUpdating = True
End Sub
[/CODE]