I am trying to retrieve data from another file using the VLOOKUP function however this is only to happen depending on if any of the 3 items of data appear in column 8(H)

OLY
OLY - QUO
OLY - PRO


I have the following and know this is not correct


Sub BlockAllocationsVlookupAll()

Dim x As Long For x = 1 To 65536

If InStr(1, Sheet1.Range("$H$" & x), "OLY") > 0 Then Sheet1.Range("$I$" & x) = Sheet1.Range("$I$" & x) & "sometext" End If Next

End Sub


I know the above doesn't do exactly what I need can anyone help as to what needs to be edited to include the Vlookup below


=VLOOKUP(A21,'[001 - Allocations - Blocks.xls]CurrentDayAll'!$1:$65536,9,FALSE)


The other issue is that the cell the VLOOKUP points to first will also change due to the varying length of the report

Thank you for any help given