Add lookup to for next

[Steph]

Hello. Conceptually I know what to do, but I do not know the proper syntax
to do it. Essentially, I would like to add a Vlookup to the end of the
statement
Cells(i, 1).Value = Cells(i, 1).Value & "|" & Cells(i, j).Value
Where Cells(i, j).Value is the lookup value. So I want to do a vlookup and
find Cells(i, j).Value in Thisworkbook.worksheets("PO
List").Range("D5:H100"), and return column 5. In the code below, I threw in
the vlookup formula at the end of the line, but obviously that doesn't work.
Can you help? Thank you!!


For j = 2 To iLastCol
If Cells(i, j).Value <> "" Then
Cells(i, 1).Value = Cells(i, 1).Value & "|" & Cells(i,
j).Value & "=VLOOKUP('[Contractor Master6.xls]HR
DB'!R8C12,'[Contractor Master6.xls]PO Table'!R5C4:R17C8,5,0)"
End If
Next j

 

[Guest]

Just use a find and offset similar to this (untested)

Dim rngFound As Range
Dim rngToSearch As Range

Set rngToSearch = ThisWorkbook.Worksheets("PO List").Range("D5:H100")
Set rngFound = rngToSearch.Find(Cells(i, j).Value)

If Not rngFound Is Nothing Then
Set rngFound = rngFound.Offset(0, 5)
rngFound.Select
End If

A little easier to follow in my opinion.
HTH