VBA Excel Vlookup

[12Rev79]

Dear Expert,

I am not good in VBA but I tried the code below so far it works somehow
except the Vlookup Value that Meal 8 = 40.5 and Meal 3 =25.6
1. how can fix the code to have the correct value
2. how can I improve my Code if Quantity Oder increase should (Order Quantity x Amount)
Ex. Meal 6 Order Qty 2 Amount = 59.26

Sub myVLookUp()
'test vlookup
Range("M5").Select
Range(Selection, Selection).End(xlDown).Offset(0, 1).Select
Range(Selection, Selection).End(xlUp).Offset(1, 0).Value = "Meal 3"
Range(Selection, Selection).End(xlUp).Offset(0, 1).Value = 1
Range(Selection, Selection).End(xlUp).Offset(0, 2).Select

Dim lkvalue As Range
Dim tblArr As Range

Set lkvalue = Range("N6:N1000")
Set tblArr = Range("U6:V1000")

ActiveCell = Application.WorksheetFunction.VLookup(lkvalue, tblArr, 2, False)
Range(Selection, Selection).End(xlToLeft).Offset(0, 1).Select

End Sub

Thank you in advance your help,

12Rev79

 

[queuesmef]

One issue may be that your lookups are not in ascending order. Please sort all the 'Menu"/ Meal items...after Meal 1 comes Meal 10, which can throw off you Vlookup. I used the following code:

Sub Vlkup()
    Dim c As Range
    For Each c In Range("m6:m15")
        If c <> "" Then
        c.Offset(0, 2) = WorksheetFunction.VLookup(c, Range("U7:V17"), 2, False)
        End If
    Next c
End Sub

you may need to modify your range? Please let me know if this has helped you.

 

[12Rev79]

Thank you queuesmef it works will.