Import info from closed workbook

**Jamesraywebber85** · 10-25-2018, 04:19 PM

I am using the following code that pulls numbers from a closed book based on book name with the cell. It keeps showing error at line: "LR = ExecuteExcel4Macro("match(" & myMax & "," & myFormula & "c1:c1,1)")" - any help would be greatly appreciated as I am not too familiar with VBA very much. This is the full code that I am using and I am attaching my workbook so you can see what this supposed to do.

Please Login or Register  to view this content.

For my example: The workbook called "Danielle Barrigar" would pull the numbers & finds the name in workbook "Captain" on sheet "ART Yearly Numbers" and put based off the date in the agents sheet. Any help would be greatly appreciated.

**Leith Ross** · 10-25-2018, 06:12 PM

Hello Jamesraywebber85,

The Excel4Macro Match follows the same rules as the Excel Match function. In your case, if the array in the closed workbook is not sorted in ascending order then you will receive an error.

Also, if Match fails to find a match then an #N/A error is returned. This will generate another error in the VBA code since you have dimension LR as Long. You would need to dimension LR as Variant to accept either a number or error value. Of course, you would then need to check if LR returned a error value in your code and provide a handler for the error.