Hello,
I have a program that identifies a product that is being used. If the conditions are met, then it will take the unit cost of each component of the finished good and adds it up and adds it to the finished good - giving the Finished Good it's respective unit cost.
I'm having a problem adding up the values of the components. How do I get the sum of all of the loops? Here's the code, I included comments on the relevant 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, XX As Range, count As Long, YU As Integer
Dim Dub As Integer
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, 9) = "Yes") Then
Else
' Wk.Cells(1, 9) = "Yes"
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
XX.Offset(0, 11) = XX.Offset(0, 11).Value - BM.Offset(0, 4).Value
YU = BM.Offset(0, 9) ' Here is the problem code
' I want to find the sum of YU and add it to XX.Offset(0, 14)
End If
Next
End With
End If
Next
End With
ElseIf (Wk.Cells(1, 6) = "Partial") 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
XX.Offset(0, 11) = XX.Offset(0, 11) - (Wk.Offset(0, 6).Value * BM.Offset(0, 5))
End If
Next
End With
End If
Next
End With
End If
End If
Next
End With
End Sub
Bookmarks