VBA Vlookup from another Workbook

[aeddipa]

I am trying to get a dollar amount generated in this one sheet that comes from data in another workbook. I have multiple files and they each change in file name so I cant use the typical VLOOKUP(value, table, column, true/false) because the table name changes.

I am attempting this in VBA and would like to Loop it as I have multiple rows. How am I able to specify I want the vlookup to reference the other workbook that is currently open. Any way to do this?

ActiveCell.FormulaR1C1 = _
        "=IF(RC[-4]="""","""",VLOOKUP(CONCATENATE(RC[-5],RC[-4],R37C34),'[Alpena Hospital - Facility.xls]Sheet1'!R6C6:R247C7,2,FALSE))"
    Range("AH38").Select
    Selection.Style = "Currency"

As you can see from the macro recorder this won't work. I need this result, but without the file name. What is a general way to essentially activate the other workbook during the vlookup and point to that source of data? Or am I looking at something that just isn't possible.

 

[aeddipa]

So I have searched the intarwebs for a solution, but haven't been able to find one. Can someone answer this: is it possible to have a VLOOKUP in VBA without naming the source file? IE. The Aplena Facility file in my previous post is required in order for the function to work?

Any help would be greatly appreciated. This is the last battle I have for this project.

 

[Joe4]

When you first open the "other" file, you can capture important things like file name, sheet name, and range addresses like this:

    myFileName = ActiveWorkbook.Name
    mySheetName = ActiveSheet.Name
'   Pick some cell in your lookup range, and use Current Region to select the rest of the range
    myRangeName = Range("A1").CurrentRegion.Address

You can then go back to your main sheet and enter your VLOOKUP function via VBA code like this:

    Windows("Book1.xlsm").Activate
    Range("B1").Formula = "=VLOOKUP(A1,[" & myFileName & "]" & mySheetName & "!" & myRangeName & ",2,0)"

obviously tailoring to fit your situation.

 

[aeddipa]

Thanks for the wonderful response! Much appreciated.

 

[Aliq2014]

can the above code be applied to this:

Hello I have the question. Im currently working to automate a process for a spreadsheet that would be populating values using vlookup in<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym>. Two workbooks are being used A) parking_template.xlsm and B) EFMA_template.xlsm. I want to populate a "Value" in column BC in the parking template workbook. Currently I have a vlookup formula that does this "=VLOOKUP(AJ:AJ,'[EFMS _Template.xlsm]Sheet1'!$B:$H,6,FALSE)". Im new with <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> and trying to come up with my 1st macro. Any help will be appreciated. FYI, both workbooks are dynamic.