VBA If Vlookup

Hi all.

I need a help with transferring excel function in to VBA code. In column G in sheet "List" I have the below formula.

VBA If Vlookup.xlsb

Formula:

Please Login or Register  to view this content.

In this formula, every value in column B in sheet "List" is compared with the range "A2:B26" in "Pickup list" sheet to get the "Type" value.
e.g.:
A1 = CB
A2 = CB
A3 = AT
A4 = AT
A5 = CB
A8 = WHS
A9 = WHS

If it get result "WHS", Its looking in range "E2:H10" in sheet "Pickup list" which value is in the 3rd column if the range if the lookup value is in column "E".
If it get result "CB", Its looking in range "E2:H10" in sheet "Pickup list" which value is in the 4th column if the range if the lookup value is in column "E".
If the value in column "E" can not be found in the range "E2:E10" in sheet "Pickup list", then it will use the first lower value.
If the model in column "B" in sheet "List" is not "CB" or "WHS", then the value should be 0.

e.g.:

Type NOPR MAXRESVP
CB 49 0.37
WHS 24 0.5
ZZ 108 0
CB 104 0.15
CB 105 0.15
CB 114 0.15
WHS 114 0.07
ZZ 34 0
CB 47 0.45
ZZ 22 0



I wanted to transfer this formula to VBA, but it does not work.

Please Login or Register  to view this content.

Can someone help me with this.

Thank you
Igor