Use VLOOKUP Depending On Certain Conditions

[TkdKidSnake]

Hi all,

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 appears

OLY
OLY - QUO
OLY - PRO

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:$<wbr style="color: rgb(31, 73, 125); font-family: Calibri, sans-serif; font-size: 15px;">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

 

[TkdKidSnake]

SOLVED


<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">Sub BlockAllocationsVlookupAll()
'
Dim x As Long
Dim lastrow As Long
Dim searchCriterias As String
Dim wb As Workbook
Dim ws As Worksheet
'
Set wb = Workbooks("Allocations.xls")
Set ws = wb.Worksheets("Current Day")
'
searchCriterias ="|OLY|OLY - PRO|OLY - QUO|"

With ws
lastrow =.Cells(.Rows.Count,"H").End(xlUp).Row
For x =3 To lastrow
If Not IsError (Application.Find("|"&.Range("H"& x).Value &"|", searchCriterias))Then
.Range("I"& x).Formula ="=VLOOKUP(A"& x &",'[001 - Allocations - Blocks.xls]CurrentDayAll'!$A:$I,9,FALSE)"
End If
Next
End With
'
Set wb =Nothing
'
EndSub</code>