markkeith
08-18-2020, 03:06 AM
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.
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.