VLOOKUP function in VB
 

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.

VLOOKUP function in VB
 

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)

"ak" wrote in message
...
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.

VLOOKUP function in VB
 

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.



"Bob Phillips" wrote:

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)

"ak" wrote in message
...
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.

VLOOKUP function in VB
 

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

ak wrote:

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.

"Bob Phillips" wrote:

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)

"ak" wrote in message
...
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.




--

Dave Peterson

VLOOKUP function in VB
 

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.

Dave Peterson wrote:

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

ak wrote:

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.

"Bob Phillips" wrote:

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)

"ak" wrote in message
...
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.




--

Dave Peterson

--

Dave Peterson

VLOOKUP function in VB
 

Thanks.

"Dave Peterson" wrote:

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.

Dave Peterson wrote:

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

ak wrote:

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.

"Bob Phillips" wrote:

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)

"ak" wrote in message
...
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.




--

Dave Peterson

--

Dave Peterson