Vlook from Closed workbook taking a lot time

[sg2209]

Dear Friends ,

i am new in VBA and trying to learning an grabs new things by going through the Forums Discussions & by watching videos , just prepare a code , that look up from the closed work book unfortunately it is taking around 10 minutes to get the values also , there are duplicate entries in Look up array table ,so it only picks up the blank cell because vVlook from gets the value of First Cell is there any way if Vlook array table has any value then need to get it instead of blank .

below is the Code
[With Sheets("Refund Payable report")
[ilastrow = .Cells(.Rows.Count, "A").End(xlUp).Row]
With .Range("V2:V" & ilastrow)
.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-21],'E:\Brm\[All Open Credits.xlsb]Sheet1'!R1C3:R468196C9,7,FALSE),""NA"")"
.Value = .Value
End With
End With




With Sheets("Refund Payable report")
ilastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("W2:W" & ilastrow)
.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-22],'E:\Brm\[All Open Credits.xlsb]Sheet1'!R1C3:R468196C9,11,FALSE),""NA"")"
.Value = .Value
End With
End With

Please Review .

[Jan Karel Pieterse]

A VLOOKUP to a closed Excel file will always be slow. I would suggest to pull in the table you are doing the VLOOKUP against using -for example- Data, Get external data, from Excel and then do the lookup against the pulled-in information.

[sg2209]

I have written another code and it opened the file , Look up and then close it , however i am not getting any values in the result , i did post the query to other Forums unfortunately not getting any resolutions , Should i allow to post it here ?

[jolivanes]

What is multiposting?
Multiposting is the act of posting a single question to different forums around the same time.
We discourage multiposting because it is like calling five cab companies and going with the one that comes first -- it shows disrespect for the volunteers that monitor this forum because they will unknowingly be helping someone who may have already received help elsewhere. Their time is no less important than that of the person asking the question.
Many of the volunteers here visit multiple forums and can easily spot duplicate posts. Some of them may even reply to your posts with a link to your post on another forum. Don't be the person that gets caught.
If you must post your question on a different forum, include a link to the question you have already posted on the previous forum(s). That way, those helping you can decide for themselves if you are already receiving the help you need somewhere else.
If you are still confused, read https://www.excelguru.ca/content.php?184

[sg2209]

Agreed , thank you