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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

delmarpt

New Member
Joined
Jul 11, 2014
Messages
16
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.
 

Aliq2014

New Member
Joined
Sep 10, 2014
Messages
46
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>
 

delmarpt

New Member
Joined
Jul 11, 2014
Messages
16

ADVERTISEMENT

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
 

Aliq2014

New Member
Joined
Sep 10, 2014
Messages
46
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.
 

delmarpt

New Member
Joined
Jul 11, 2014
Messages
16

ADVERTISEMENT

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.
 

Aliq2014

New Member
Joined
Sep 10, 2014
Messages
46
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!
 

Aliq2014

New Member
Joined
Sep 10, 2014
Messages
46
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,596
Messages
5,832,638
Members
430,149
Latest member
amitk1

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
Top