Loop Vlookup for each cell in range from another worksheet

[markkeith]

I'm trying to loop Vlookup using for each cell in range from another worksheet but all lookup_value is getting results even though it doesn't MATCH with the data from the table_array.


When button IN was clicked, I want the quantity from each cell of table_array to add the quantity in corresponding lookup_value cells.



Private Sub cmdIN_Click()
Dim rng As Range, cell As Range
Set rng = Sheet2.Range("prodDesc")


If MsgBox("Are you sure you want to update the stock IN quantity?", vbQuestion + vbYesNo, "Fresh Herbs & Spices") = vbNo Then
Cancel = True
End If

Application.EnableEvents = False
Application.ScreenUpdating = False
Sheet2.Unprotect Password:=""


On Error Resume Next 'on error run code on next line
For Each cell In rng
cell.Offset(, 13).Value = [VLookup(prodDesc, descTable, 2, False)] + cell.Offset(, 13).Value
Next cell

Sheet2.Protect Password:="", DrawingObjects:=True, Contents:=True, Scenarios:=True
Cells(Rows.Count, "C").End(xlUp).Offset(3).Select


Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


However the result is NOT as expected, because all cells in lookup_value (prodDesc) are getting same result with or without MATCH from table_array (descTable).
By the way, prodDesc & descTable are Dynamic Table.

[p45cal]

Private Sub cmdIN_Click()
Dim cell As Range, qty

If MsgBox("Are you sure you want to update the stock IN quantity?", vbQuestion + vbYesNo, "Fresh Herbs & Spices") = vbYes Then
Application.EnableEvents = False
Application.ScreenUpdating = False
Sheet2.Unprotect Password:=""

For Each cell In Sheet2.Range("prodDesc")
qty = Application.VLookup(cell.Value, Range("descTable"), 2, False)
If Not IsError(qty) Then cell.Offset(, 13).Value = qty + cell.Offset(, 13).Value
Next cell

Sheet2.Protect Password:="", DrawingObjects:=True, Contents:=True, Scenarios:=True
Cells(Rows.Count, "C").End(xlUp).Offset(3).Select

Application.ScreenUpdating = True
Application.EnableEvents = True
End If
End Sub

[markkeith]

Thank you very much p45cal for fixing my code :clap:, you really saved my day I'm stuck on that issue for a few days :(.