couple problems with this macro: A) random Product ID entry on invoice will stop the macro from running after the first inventory update. Product must be listed on the invoice in the same order as iit's listed on the product sheet.
B) Macro stops on empty row.
I need this macro to run regardless of entry order and ignore empty cell and run to the last row.
I have very limited VBA skill, I would really appreciate any help.
Thank you.
Sub updateInventory()
Dim x As Long
Dim r As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim newQuantity As Long
Dim answer As Integer
Set ws1 = Worksheets("Invoice")
Set ws2 = Worksheets("Inventory")
answer = MsgBox("Are you sure you want to update Inventory?", vbYesNo + vbQuestion, "Empty Sheet")
x = 19
r = 2
If answer = vbYes Then
For Z = 1 To 14
If ws1.Range("C" & x).Value = ws2.Range("B" & r).Value Then
newQuantity = ws2.Range("C" & r).Value - ws1.Range("E" & x).Value
ws2.Range("c" & r).Value = newQuantity
x = x + 1
Else
r = r + 1
End If
Next Z
x = x + 1
Else
End If
End Sub
Bookmarks