PDA

View Full Version : Vlookup from external file



JohnBradley
09-14-2014, 01:19 AM
Hi guys,

I need help about vlookup vbs code, please help if you could...

The vba code need to call table from external file and represent results in current file.

To be more specific

I have an excel file (1) that has numbers in column A something like that:

COLUMN A
3232134
4563455
6565656

I have a second excel file (2) in my PC in this path W:\Documents\Files his name is "compare" that also with my (1) file numbers in column A and other serial of numbers in the same rows but column B, something like that:.


COLUMN A COLUMN B
3232134 1111111
4563455 2222222
6565656 3333333

From him I need to create a vlookup what will compare and push these numbers of file (2) column B to file (1) column A

Thanks in advanced.
John.

p45cal
09-14-2014, 08:21 AM
in cell B2 of file(1), something like:
=VLOOKUP(A2,'W:\Documents\Files\[SampleFileName.xlsx]Sheet1'!$A:$B,2,FALSE)
and copy down. Adjusted of course for your file name and sheet name.

JohnBradley
09-14-2014, 11:31 AM
in cell B2 of file(1), something like:
=VLOOKUP(A2,'W:\Documents\Files\[SampleFileName.xlsx]Sheet1'!$A:$B,2,FALSE)
and copy down. Adjusted of course for you file name and sheet name.

Hi,

thanks.. I know this formula because i am doing this vlookup procedure manually, but i want to do it automatically with vba code.

Can you assist me with the code?

thanks.

p45cal
09-14-2014, 01:08 PM
Sub blah()
Range("B2:B60").FormulaR1C1 = "=VLOOKUP(RC[-1],'W:\Documents\Files\[SampleFileName.xlsx]Sheet1'!C1:C2,2,FALSE)"
End Sub
adjusted for destination range, sheet name and file name.
I don't know how much you want to automate; source file name to be picked in a dialogue box? determine destination range (but I don't know what your sheet has in it so don't know best way of doing this)?, source sheet name?, etc. etc.