Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Value
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Value
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 -- Regards, Tom Ogilvy "Joe" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Value
I receieved a syntax error for the line
set rng = .Range(.Cells(2,2,.Cells(rows.count,2).End(xlup)) Joe "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Joe" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Value
Its missing a right paren after 2,2
Set Rng = .Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp)) should fix it. -- Regards, Tom Ogilvy "Joe" wrote in message ... I receieved a syntax error for the line set rng = .Range(.Cells(2,2,.Cells(rows.count,2).End(xlup)) Joe "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Joe" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Lookup looks to the prior column if zero appears in the lookup col | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |