VBA using lookup between multiple workbooks.

Aliq2014

New Member
Joined
Sep 10, 2014
Messages
46
Hello I have the question. Im currently working to automate a process for a spreadsheet that would be populating values using vlookup in vba. 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 vba and trying to come up with my 1st macro. Any help will be appreciated. FYI, both workbooks are dynamic.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Use Application.WorksheetFunction.VLOOKUP

Code:
Application.WorksheetFunction.VLOOKUP(Cells(1, 1), Workbooks("WorkbookToBeSearched.xlsx").Worksheets("SheetToBeSearched").Range("A:A"), 1, False)
Note: The Workbook to be searched must be opened.
 
Upvote 0
Thank you delmarpt,

I'm really new into programming trying to become familiar with vba. Do i need to change the range?


Application.WorksheetFunction.VLOOKUP(Cells(1, 1), Workbooks("WorkbookToBeSearched.xlsx").Worksheets("SheetToBeSearched").Range("A:A"), 1, False)</pre>
 
Upvote 0
Get the files in the link bellow: Fast Files - Share your files for FREE!

Here's the full code:
Code:
Sub FindVal()

Dim x, lRow As Long


lRow = Cells(Rows.Count, 1).End(xlUp).Row 'to find the last row


On Error Resume Next


For x = 1 To lRow
    Cells(x, 2) = Application.WorksheetFunction.VLOOKUP(Cells(x, 1), Workbooks("EFMA_template.xlsm").Worksheets("Folha1").Range("A:A"), 1, False)
Next x


End Sub
 
Upvote 0
Thank you so much! I will make sure I step into the code to trace it and get more up the speed into the vba world.
 
Upvote 0
Yes, you'll need to change to to where the value is:

Workbooks("EFMA_template.xlsm").Worksheets("Folha1").Range("A:A"), 1, False

Change the range in blue to the column where the value you want exists.
The red number means the position number in the range, in this case 1 because there is only 1 position between A and A.

Note: For the code to work, the 2 workbooks must be opened.
 
Upvote 0
Great! yes the range was the 1st thing that fought my attention since I/m really familiar with excel formulas. Thanks you so much! again!
 
Upvote 0
Hello, I updated the code to suit my needs. However, it didn't work for me. Might be something not complex as I think it is.
 
Upvote 0

Forum statistics

Threads
1,216,095
Messages
6,128,792
Members
449,468
Latest member
AGreen17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top