eds000


I am using vlookup and I want to obtain the value in column B for the second occurance of the lookup value This code comes up with error

DestCity = WorksheetFunction.VLookup("Line#1 CITY", Range("CPARSdata"), 2, 0) _
And WorksheetFunction.VLookup("Line#1 CITY", Range("CPARSdata"), 2, 0)

If I use the first part before the "And" it will find the results to the first occurance.

Please help!




Re: vlookup

ADG


Hi

Try a change of tack. Try using the Find command to go to the second instance, then get the value from column B in the found row. Example of finding the secind instance of "Bosch" below:

Columns("A:A").Select
Selection.Find(What:="BOSCH", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.FindNext(After:=ActiveCell).Activate






Re: vlookup

Peter Mo.

Hi

As an alternative if you like WorksheetFunctions you could try

With Range("CPARSdata")
DestCity = WorksheetFunction.VLookup("Line#1 CITY", .Range(.Cells( _
WorksheetFunction.Match( _
"Line#1 CITY", .Range(.Cells(1, 1), .Cells(.Rows.Count, 1)), 0) + 1, _
1), .Cells.SpecialCells(xlCellTypeLastCell)), 2, False)
End With

Regards

Peter Mo.