Hello,
For some reason the code below works, except for on the loop. I've included the workbook that I'm working on, the module name is WIP_Complete. It's supposed to subtract the quantity from the Item Master list from the quantity used in the Inventory Allocation tab for the respective component (e.g., Dried Hibiscus). I have it working except it gets the wrong output cell during the last loop. Rather than outputting in the correct cell, it does so 1 below and I have no idea why.
Here's the code:
Option Explicit
Sub Invo()
Dim InvAllocWS As Worksheet
Dim BillMatWS As Worksheet
Dim ItemMas As Worksheet
Dim WorkProWS As Worksheet
Dim Wk As Range, BM As Range, CP As Range, XX As Range
Set InvAllocWS = Worksheets("Inventory Allocation")
Set ItemMas = Worksheets("Item Master")
Set BillMatWS = Worksheets("Bill of Material")
Set WorkProWS = Worksheets("Work in Process")
With WorkProWS
For Each Wk In Range(.Cells(3, 4), .Cells(Rows.Count, 4).End(3))
If (Wk.Cells(1, 6) = "Yes") Then
With InvAllocWS
For Each BM In Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(3))
If (BM = Wk) Then
With ItemMas
For Each XX In Range(.Cells(3, 4), .Cells(Rows.Count, 4).End(3))
If (XX = BM.Cells(1, 4)) Then
MsgBox XX.Offset(1, 11).Value
XX.Offset(1, 11) = XX.Offset(1, 11) - BM.Offset(0, 4).Value
End If
Next
End With
End If
Next
End With
End If
Next
End With
End Sub
Bookmarks