Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

VBA error in modified version of search multiple files


Hello! In the following program, sometimes, at some files that I search, I get an error at this line

If (dataArray(k, m) = searchValue) Then

Does someone have any ideea? Are there any flaws?

I am searching in different types of files, for a specific name, and it returns me the data about the name. But yet they have similar headers, althought it shouldn't matter, because the name I search is from the same column every time.

Error is:

Run-time error '9':

Subscript out of range




I can't answer you! You haven't said what error is reported by VBA nor provided a searched file where the failure occurs. Also, when you say "different types of files", they are all Excel workbooks, right?

Please edit your question to clarify/ provide another file.
John_Ru (rep: 2857) Dec 3, '21 at 6:41 am
I edited and posted the error. And yes, they're all excel files.
Squishy (rep: 18) Dec 3, '21 at 6:44 am
Bit puzzled by this one! I think your code doesn't limit name finds to columns with the same heading (FieldName)- is that intentional?

Also you have k and m loops so k will never exceed 43 in your dataArray(k, m) so you'd miss any rows beyond that (e.g. if your file has 100 populated rows, you'd miss the last 57 of them)
John_Ru (rep: 2857) Dec 3, '21 at 7:27 am
Squishy, I think I've worked it out (got your code working) but need 1) answers to the above, 2) you to change question title to something more specific e.g. "VBA error in modified version of search multiple files"
John_Ru (rep: 2857) Dec 3, '21 at 8:03 am
Please see above but I will post an answer anyway
John_Ru (rep: 2857) Dec 3, '21 at 8:21 am
Yeah, I put the loop like that just for now, it is not finished, I'll later increase the value. And ok, I'll change the title
Squishy (rep: 18) Dec 6, '21 at 4:26 am
Hi Squishy. Thanks for chnaging the question title. Did my answer work for you?
John_Ru (rep: 2857) Dec 6, '21 at 4:29 am
Hello again. Yes, it does work very well! Can't thank you enough! I have just 1 more little question. You think I should open a new topic or write it here? That's the only one I have left and then I can say I finished the program I needed:) with your enormous help, of course.
Squishy (rep: 18) Dec 6, '21 at 4:39 am
Glad it worked. 

Depends how little the question is! Put it below please and if I can answer it without thinking much I will.
John_Ru (rep: 2857) Dec 6, '21 at 4:45 am
If I want to search a name from a file, in multiple files, when I copy and paste it in the search field, when I hit search, macro starts and closes the file where I got the name from. How can I make it to leave the file opened, is it possible? Because my files are huge and it takes 1-2 minutes to open, and I don't want to open them again and again:)
Squishy (rep: 18) Dec 6, '21 at 4:48 am
New question please
John_Ru (rep: 2857) Dec 6, '21 at 4:54 am
Ok, I'll open a new one. Thanks!
Squishy (rep: 18) Dec 6, '21 at 4:56 am
Add to Discussion


Selected Answer


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:

  1. data validation has been removed for Dashboard cell F4 (for my test purposes)
  2. 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.

Answer the Question

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