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.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,507
Messages
5,523,309
Members
409,510
Latest member
HQ2401

This Week's Hot Topics

Top