VBA Code For Vlookup   

Hi,

I will need your help to give me correct VBA code for Vlookup.

I have one workbook which has two worksheet Named "Sheet1" & "Sheet2"

In "Sheet1" I have all main data. In "Sheet2" I have another data which I 
want compare. I want to compare data in column "G" with data in "Sheet2" 
Please note that in "Sheet2" I have 4 column & i want output from 4th column 
in "Sheet2" in "Sheet1" in column "W".

Sheet1 :
Column G                  Column H                  Column I
CustomerCode	CustomerName	Amount
130LPL18933	KETER UK LTD	6894.5	
130GLA16122	FOXTEQ UK LTD	2175	

Sheet2 :
Column A                  Column B                   Column C    Column D
Customer Number	Customer Name	City	  Businessarea.Responsible
130LPL18933              MCEWAN, QC	GIRVAN	  Scotland Business 
130GLA16122             NOMADS TENT	EDINBURGH Scotland Business 

Please note that I have used VBA code but it's giving me error as "#NAME?"
.Range("W2:W15000").FormulaR1C1 = "=VLOOKUP(RC7,Sheet2!A:D,4,FALSE)"

Your help is really appreciated.

Thanks & Best Regards,
Kam.

date: Thu, 20 Sep 2007 02:56:22 -0700   author:   Kam

Re: VBA Code For Vlookup   

=?Utf-8?B?S2Ft?=  wrote in
news:AFE83DDB-5F76-435B-87A4-C33DC6E9ED67@microsoft.com: 

> Hi,
> 
> I will need your help to give me correct VBA code for Vlookup.
> 
> I have one workbook which has two worksheet Named "Sheet1" & "Sheet2"
> 
> In "Sheet1" I have all main data. In "Sheet2" I have another data
> which I want compare. I want to compare data in column "G" with data
> in "Sheet2" Please note that in "Sheet2" I have 4 column & i want
> output from 4th column in "Sheet2" in "Sheet1" in column "W".
> 
> Sheet1 :
> Column G                  Column H                  Column I
> CustomerCode     CustomerName     Amount
> 130LPL18933     KETER UK LTD     6894.5     
> 130GLA16122     FOXTEQ UK LTD     2175     
> 
> Sheet2 :
> Column A                  Column B                   Column C   
> Column D Customer Number     Customer Name     City      
> Businessarea.Responsible 130LPL18933              MCEWAN, QC    
> GIRVAN       Scotland Business 130GLA16122             NOMADS TENT    
> EDINBURGH Scotland Business 
> 
> Please note that I have used VBA code but it's giving me error as
> "#NAME?" .Range("W2:W15000").FormulaR1C1 =
> "=VLOOKUP(RC7,Sheet2!A:D,4,FALSE)" 
> 
> Your help is really appreciated.
> 
> Thanks & Best Regards,
> Kam.

Kam,

I would create a loop and then input the formula to each cell in turn. 
If you've got a half decent machine, it won't take that long to run.
Something like this:

Range("W2").Select

For x = 1 To 15000
    	ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],Sheet2!C[-22]:C[-
19],4,FALSE)"
    	ActiveCell.Offset(1,0).Select
Next

Darrel

date: Mon, 24 Sep 2007 22:30:23 -0700   author:   Darrel Couzens