jeudi 30 juillet 2015

Nested If Wont Trigger When True

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