Excel VBA Vlookup using 2 workbooks

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
145
Hi, I'm new to VBA and don't quite really understand much, I apologize, I'm trying to do a Vlookup from another workbook.

All I want to do is in cell b9 in my current workbook, search for values in another workbook (Finance Extract.xls) from cells d9 to last cell and return a value
Here is the code I used but it's returning a zero (0) in each row in column b

Range("b9:b" & lRow).Formula = "=iferror(VLOOKUP(d9,Finance Extract.xls!d:d,4,FALSE),0)"

What am I doing wrong?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Re: VBA Vlookup from another Workbook

You are referencing the third argument, col_index_num as 4, but yet your table array is one column. Column D.

I suspect you may be looking up the value from the Finance Extract workbook in column A and then returning the fourth column over to the right.

Untested, try

Code:
[LEFT][COLOR=#333333][FONT=Verdana]Range("b9:b" & lRow).Formula = "[COLOR=#222222][FONT=Verdana]=IFERROR(VLOOKUP(D9,'[Finance Extract.xls]Sheet1'!$A:$D,4,FALSE),0)"[/FONT][/COLOR][/FONT][/COLOR][/LEFT]
Maybe it would also help to get the spreadsheet formula to work first and then go for the VBA code.
 
Last edited:
Upvote 0
Re: VBA Vlookup from another Workbook

I'm looking up the values on the finance extract on column D and returning those values on my current workbook in column D

The code you provided is not working
 
Upvote 0
Re: Translate Vlookup to vba code

Your code will probably be putting the formula referencing d9 in every row, rather than increasing the row number by one each time. So all rows will show the same result. You need your code to use the row number of the cell that you're inputting the formula in. Try:
Rich (BB code):
For each Cell in 
Range("b9:b" & lRow) Cell.Formula =
"=iferror(VLOOKUP(d" & Cell.Row & ",Finance Extract.xls!d:d,4,FALSE),0)" Next
<strike>
</strike>
 
Upvote 0
Re: Translate Vlookup to vba code

@tanyaleblanc
Please do not post the same question multiple times. Per forum rules (rule 12 here: Forum Rules).

I have merged all your threads together
 
Upvote 0
Re: Translate Vlookup to vba code

I updated the code to yours,

Range("b9:b" & lRow).Formula = "=iferror(VLOOKUP(d" & 9 & ",Finance Extract.xls!d:d,4,FALSE),0)"

returns 0 in each cell in b9, not sure why
 
Upvote 0
Re: Translate Vlookup to vba code

What is the name of the sheet in the Finance Extract workbook that you want to lookup?
 
Upvote 0
Re: Translate Vlookup to vba code

Try
Code:
Range("B9:B" & lrow).Formula = "=iferror(VLOOKUP(D9,'[COLOR=#ff0000]C:\mrexcel\[/COLOR][Finance Extract.xls][COLOR=#0000ff]all[/COLOR]'!D:G,4,FALSE),0)"
Change the part in red to match the filepath of your file & change the part in blue to match the sheet name containing the data
 
Upvote 0
Re: Translate Vlookup to vba code

I put in the above and changed the file path location and change all to sheet1 but still not working,

Range("B9:B" & lRow).Formula = "=iferror(VLOOKUP(D9,'I:\SSC Operations\004 Group\3 Order to Cash\OTC REPORTING\Maxium\(2) 2019 Maxium\(2) February 2019[Finance Extract.xls]sheet1!D:G,4,FALSE),0)"
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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