Search a range from Selected Closed workbooks for specific value and return offset values.

Hello, I have come across some code from Ron de Bruin that could be extremly usefull , but need help amending it.
The code enables you to copy a specific range from a number of closed workbooks and paste that range into a new workbook (would prefer to paste into current worksheet if possible).
My aim is to be able to search for a specific value or values within that specified range from the closed workbooks and return a number of offset values, creating the required list within the new workbook (current worksheet prefered, so I can use a template).
I already use a different code that searches for a specific value within the same workbook and returns offset values, but have had no luck merging the two after many hours of trying.
Really hope someone is able to help.
This is the Ron de Bruin code:

Please Login or Register  to view this content.

And this is my other code:

Please Login or Register  to view this content.

I know the second code is not very impressive, so if the same effect could be acheved a better way with the first code then even better.
Hope someone can help before I pull all of my hair out. lol
Thanks
Andrew

The code below seems to be putting the offset values into the opened workbook from the loop where its getting the data from rather than in the workbook im running the code from?
Someone help!

Please Login or Register  to view this content.

Give this a shot:

Please Login or Register  to view this content.

It takes a slightly different approach. Same as the other, opens a file dialog and allows you to select multiple files. Loops through the selected files, loops through each worksheet in each selected file, then copies the entire range of each worksheet into an array. Loops through the array, looking for X in either AE or AH, when it finds the X, adds a row to the output array and moves the selected data to the output array. At the end, moves the entire output array to your base sheet.

If you have a lot of data, the .Transpose will fail, one work-around would be to move the data after processing each workbook or each worksheet.

I don't have anything to test this work, so give it a shot, let me know how it goes.

Hey Wallyeye,
Thanks for this, doesnt seem to extract the data? Code runs, but nothing happens.
This was my attempt: might give you more idea of what im trying to achieve.

Please Login or Register  to view this content.

But mine code below seems to be putting the offset values into the opened workbook from the loop where its getting the data from rather than in the workbook im running the code from?

I noticed that in this code you are looking for "001" in column D, in the other code you were looking for an X in AE or AH. Which do you really want to do?

Hey Walleye, sorry about that, at the moment its 001 for D, but ideally I want to use the code for varying values and columns, I may even end up using something like the ActiveCell comand or a specific cell value to specify the search criteria, so it can be changed by the user.

So, let's do the generic version:

Please Login or Register  to view this content.

Call this from your procedure, passing to it the variable you are looking for, and the column in which it should be found:

Call ImportData(Worksheets("Sheet1").Range("A1").value, Range("D:D"))

or

call importdata(Worksheets("Sheet1").range("A1").value, column(4))

Where you have the "001" in Sheet1!A1 and want to search column D of the importing file.

Wallyeye, this code is way above my head and knowledge of vba, im a bit confused! Sorry! Im running the code as a stand alone at the moment, so what do you mean call this from my procedure? Does this code have the same purpose/outcome as my previous posted code?

If you want a user to be able to specify a value to search for, you will probably have a worksheet_change event or a btnSearch_Click event associated with the worksheet where the user will be searching from. Say you use a button on the worksheet, name it btnSearch and create a btnSearch_Click event for the worksheet that looks like this:

Please Login or Register  to view this content.

Where the value to search for is in A1. The tricky part would be identifying the range to search in. Maybe have a separate drop-down with a list of the columns. The list would have a named range associated with it say ColumnNames, you could specify the indicated column something like this, for a dropdown in B1:

Please Login or Register  to view this content.

I put the +2 in, just in case you have a couple of columns that won't be searched. So, if the user selects the third item in the list, the Match function would return a 3, then the _Click event would call ImportData, passing it the value in A1 and a reference to column E (3 + 2 = 5th column, i.e. E).

Still cant get this to work, now getting a: Subscript out of range (Error 9). The Problem Im having is I dont understand the code enough to debug!

Which method are you using, and where are you receiving the error? "Subscript out of range" sounds to me like you are referring to a named worksheet or named range that doesn't exist.

For the time being I am just trying the first method, and the following line of code is where it errors:

Please Login or Register  to view this content.

I have a problem remembering to reference the right ubound...

Please Login or Register  to view this content.

should work better. While looking for this error, I noticed a line above it should change from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.