VLOOKUP function in VB

[Guest]

May I know what should be written in the VB script if I want to use VLOOKUP
function to search details from an external file.
For example,
Cells(1,1) in SheetA contains the Customer#
SheetB contains the Customer#, Customer Address, Contact

cells(1,2) in SheetA will show the corresponding Contact from SheetB after
the VLOOKUP.

Thanks in advance to the expert.

 

[Bob Phillips]

With worksheets("SheetA")
.Cells(1,2).Value =
Application.Vlookup(.Cells(1,1),Worksheets("SheetB").Range("A1:C100"),3,False)
End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob.
But how can I refer Sheet B if this is a worksheet(ex. source) in an
external excel file that I don't want to open it.

 

[Dave Peterson]

If you don't want to open that other file, you could:

Find an empty cell
plop the formula in that cell
pick up the result of the calculation
clean up that cell

 

[Dave Peterson]

In fact, I'd recommend that you open that other workbook, create the =vlookup()
formula and then close that workbook.

Then duplicate that syntax in your code.

 

[Guest]

Thanks.

In fact, I'd recommend that you open that other workbook, create the =vlookup()
formula and then close that workbook.

Then duplicate that syntax in your code.