This code loops through a column of data. If the cell in the column Is a WIP it goes to a corresponding column and loops through that data. If not it takes the data and pastes it in the summary tab. Yet for some reason it will not pick up if there Is a WIP in column R.
Can someone see why the code does not trigger my second IF.
Cheers
Sub summary_2()
Dim MainLoop As Double
Dim Secondloop As Double
Dim TopRow As Integer
Dim TopRow1 As Integer
Dim ThirdLoop As Double
Dim FourthLoop As Double
Dim ParentName As String
Dim ParentSku As Double
Dim WipSku As Double
Dim WipSku1 As Double
Dim childDesc As String
Dim childDesc1 As String
Dim childType As String
Dim childType1 As String
Range("A6" & ":" & "G1000").ClearContents
MainLoop = 5
Secondloop = 0
ThirdLoop = 0
FourthLoop = 0
TopRow = 5
TopRow1 = 5
Application.ScreenUpdating = False
Worksheets("Final").Activate
'loop 1
Do While MainLoop < ActiveSheet.Cells(Rows.count, "B").End(xlUp).Row
ParentSku = Range("A" & MainLoop).Value
ParentName = Range("B" & MainLoop).Value
Worksheets("Recipe Book").Activate
Range("C" & (ActiveSheet.Cells(Rows.count, "C").End(xlUp).Row) + 2).Value = ParentSku
Range("D" & (ActiveSheet.Cells(Rows.count, "D").End(xlUp).Row) + 2).Value = ParentName
Range("E" & (ActiveSheet.Cells(Rows.count, "E").End(xlUp).Row) + 2).Value = "Parent"
Range("F" & (ActiveSheet.Cells(Rows.count, "F").End(xlUp).Row) + 2).Value = " - "
Worksheets("Final").Activate
'loop 2
Do While Secondloop < 10
'If 3
If Range("H" & (MainLoop + Secondloop)) = "WIP" Then
WipSku = Range("F" & (MainLoop + Secondloop)).Value
'loop 4
Do While TopRow < ActiveSheet.Cells(Rows.count, "B").End(xlUp).Row
'If 5
If Range("J" & TopRow).Value = WipSku Then
'loop 6
Do While ThirdLoop < 15
childSKU = Range("P" & (TopRow + ThirdLoop)).Value
childDesc = Range("Q" & (TopRow + ThirdLoop)).Value
childType = Range("R" & (TopRow + ThirdLoop)).Value
childPKG = Range("S" & (TopRow + ThirdLoop)).Value
'Find WIPs in R (this is where the code wont pick up)
If Range("R" & (TopRow + ThirdLoop)).Value = "WIP" Then
WipSku1 = Range("P" & (TopRow + ThirdLoop)).Value
Do While TopRow1 < ActiveSheet.Cells(Rows.count, "B").End(xlUp).Row
If Range("T" & TopRow1).Value = WipSku1 Then
Do While FourthLoop < 15
childSKU1 = Range("Z" & (TopRow1 + FourthLoop)).Value
childDesc1 = Range("AA" & (TopRow1 + FourthLoop)).Value
childType1 = Range("AB" & (TopRow1 + FourthLoop)).Value
childPKG1 = Range("AC" & (TopRow1 + FourthLoop)).Value
Worksheets("Recipe Book").Activate
Range("A" & (ActiveSheet.Cells(Rows.count, "C").End(xlUp).Row) + 1).Value = ParentSku
Range("B" & (ActiveSheet.Cells(Rows.count, "D").End(xlUp).Row) + 1).Value = ParentName
Range("C" & (ActiveSheet.Cells(Rows.count, "C").End(xlUp).Row) + 1).Value = childSKU1
Range("D" & (ActiveSheet.Cells(Rows.count, "D").End(xlUp).Row) + 1).Value = childDesc1
Range("E" & (ActiveSheet.Cells(Rows.count, "E").End(xlUp).Row) + 1).Value = childType1
Range("F" & (ActiveSheet.Cells(Rows.count, "F").End(xlUp).Row) + 1).Value = childPKG1
Range("G" & (ActiveSheet.Cells(Rows.count, "F").End(xlUp).Row) + 1).Value = "3"
Worksheets("Final").Activate
FourthLoop = FourthLoop + 1
Loop
ElseIf Range("T" & TopRow1).Value <> WipSku1 Then
End If
TopRow1 = TopRow1 + 1
Loop
Worksheets("Final").Activate
ElseIf childType = "ING" Or childType = "MAT" Or RangechildType = "PKG" Then
End If
'End If for WIPs in R
Worksheets("Recipe Book").Activate
Range("A" & (ActiveSheet.Cells(Rows.count, "C").End(xlUp).Row) + 1).Value = ParentSku
Range("B" & (ActiveSheet.Cells(Rows.count, "D").End(xlUp).Row) + 1).Value = ParentName
Range("C" & (ActiveSheet.Cells(Rows.count, "C").End(xlUp).Row) + 1).Value = childSKU
Range("D" & (ActiveSheet.Cells(Rows.count, "D").End(xlUp).Row) + 1).Value = childDesc
Range("E" & (ActiveSheet.Cells(Rows.count, "E").End(xlUp).Row) + 1).Value = childType
Range("F" & (ActiveSheet.Cells(Rows.count, "F").End(xlUp).Row) + 1).Value = childPKG
Range("G" & (ActiveSheet.Cells(Rows.count, "F").End(xlUp).Row)).Value = "2"
Worksheets("Final").Activate
ThirdLoop = ThirdLoop + 1
'loop 6
Loop
'Else 5
ElseIf Range("J" & TopRow).Value <> WipSku Then
'If 5
End If
TopRow = TopRow + 1
'loop 4
Loop
Worksheets("Final").Activate
'Else 3
ElseIf Range("H" & (MainLoop + Secondloop)) = "ING" Or Range("H" & (MainLoop + Secondloop)) = "MAT" Or Range("H" & (MainLoop + Secondloop)) = "PKG" Then
childSKU = Range("F" & MainLoop + Secondloop).Value
childDesc = Range("G" & MainLoop + Secondloop).Value
childType = Range("H" & MainLoop + Secondloop).Value
childPKG = Range("I" & MainLoop + Secondloop).Value
Worksheets("Recipe Book").Activate
Range("A" & (ActiveSheet.Cells(Rows.count, "C").End(xlUp).Row) + 1).Value = ParentSku
Range("B" & (ActiveSheet.Cells(Rows.count, "D").End(xlUp).Row) + 1).Value = ParentName
Range("C" & (ActiveSheet.Cells(Rows.count, "C").End(xlUp).Row) + 1).Value = childSKU
Range("D" & (ActiveSheet.Cells(Rows.count, "D").End(xlUp).Row) + 1).Value = childDesc
Range("E" & (ActiveSheet.Cells(Rows.count, "E").End(xlUp).Row) + 1).Value = childType
Range("F" & (ActiveSheet.Cells(Rows.count, "F").End(xlUp).Row) + 1).Value = childPKG
Range("G" & (ActiveSheet.Cells(Rows.count, "F").End(xlUp).Row)).Value = "1"
Worksheets("Final").Activate
'if 3
End If
'Loop 2
Secondloop = Secondloop + 1
Loop
MainLoop = MainLoop + 20
Secondloop = 0
ThirdLoop = 0
FourthLoop = 0
TopRow1 = 0
TopRow = 5
Loop
Worksheets("Recipe Book").Activate
End Sub
Aucun commentaire:
Enregistrer un commentaire