Lookup Value

[Guest]

Using VB code, I need to be able use a lookup feature to accomplish the
following:

Row 1 is for headers. Starting at Row 2 I need to fill Column 20 based on
the text contained in contained in column 2. Column 2 must be compared to
Column 1 on sheet 2 with a Range (A2:C500). When an exact match is found
then the text in column 3 of sheet 2 needs to be returned to column 20 of
sheet 1.

Thank you. Joe

 

[Chip]

Sorry Joe but I could not follow that...

You say Column 2 needs to be compared to Column 1 on Sheet 2 (but then
why is the range you mention 3 columns?)

But let me see if I get it logically..

If Column2 data matches any data on Sheet2 Column1 then
At that cell, go on the same row accross, and get the data from column
3 and return that to Sheet 1 column 20, row being the same as the one i
was trying to get a match for

Else

What happens here?

End If

 

[Tom Ogilvy]

Dim rng as Range
Dim rng1 as Range
Dim cell as Range
Dim res as Variant

With worksheets(1)
set rng = .Range(.Cells(2,2,.Cells(rows.count,2).End(xlup))
End With
set rng1 = Worksheets(2).Range("A2:A500")
for each cell in rng
res = Application.Match(cell,rng1,0)
if not iserror(res) then
cell.offset(0,18).Value = rng1(res).offset(0,2).Value
end if
Next

 

[Guest]

I receieved a syntax error for the line

set rng = .Range(.Cells(2,2,.Cells(rows.count,2).End(xlup))

Joe

 

[Tom Ogilvy]

Its missing a right paren after 2,2

Set Rng = .Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp))

should fix it.