jeudi 23 juillet 2015

Nested Loops & Ifs, loop without Do

I keep getting a Loop without Do every time I run my code and I do not see where I am missing anything or if a loop is misplaced. I need this code to find key words in specific columns copy then paste them into a summary tab.

Your help would be greatly appreciated.

Sub Summary()

Dim MainLoop As Double
Dim SecondLoop As Double
Dim thirdLoop As Double
Dim Trow As Double
Dim counter As Integer

Dim PSKU As Integer
Dim PDesc As String
Dim PPKG As Integer

Dim CSKU As Integer
Dim CDesc As String
Dim CPKG As Integer
Dim Cstatus As String

MainLoop = 5
SecondLoop = 0
thirdLoop = 0
Trow = 5
counter = 0

Worksheets("final").Activate

    Do While MainLoop < ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
        Worksheets("Final").Activate

        ParentSKU = Range("F" & MainLoop).Value
        ParentDesc = Range("G" & MainLoop).Value

        Worksheets("Summary").Activate

            SumRow = (ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row) + 1
            Range("A" & SumRow).Value = ParentSKU
            Range("B" & SumRow).Value = ParentDesc
            Range("C" & SumRow).Value = "Parent"

            Worksheets("Final").Activate

                Do While SecondLoop < 20

                    If Range("H" & MainLoop + SecondLoop).Value = "MAT" Or "PKG" Or "ING" Then

                       Range("F" & MainLoop + SecondLoop).Value = CSKU
                       Range("G" & MainLoop + SecondLoop).Value = CDesc
                       Range("H" & MainLoop + SecondLoop).Value = (Cstatus)
                       Range("I" & MainLoop + SecondLoop).Value = CPKG

                       Worksheets("Summary").Activate

                            SumRow = (ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row) + 1
                            Range("A" & SumRow).Value = CSKU
                            Range("B" & SumRow).Value = CDesc
                            Range("C" & SumRow).Value = "Child"
                            Range("D" & SumRow).Value = CPKG


                     ElseIf Range("H" & MainLoop + SecondLoop).Value = "WIP" Then

                        Find = Range("F" & MainLoop + SecondLoop).Value

                        Do While Trow < ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row & thirdLoop < 20

                            If Range("J" & Trow).Value = Find Then

                                If Range("P" & Trow + thirdLoop).Value <> "" Then

                                    CSKU = Range("P" & Trow + thirdLoop).Value
                                    CDesc = Range("Q" & Trow + thirdLoop).Value
                                    Cstatus = Range("R" & Trow + thirdLoop).Value
                                    CPKG = Range("S" & Trow + thirdLoop).Value

                                    Worksheets("Summary").Activate

                                        SumRow = (ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row) + 1
                                        Range("A" & SumRow).Value = CSKU
                                        Range("B" & SumRow).Value = CDesc
                                        Range("C" & SumRow).Value = "Child"
                                        Range("D" & SumRow).Value = CPKG

                                        Worksheets("Final").Activate

                                    thirdLoop = thirdLoop + 1
                                    Trow = Trow + 1
                                 Loop

                             Else

                                Trow = Trow + 1

                             End If

                           Else
                            thirdLoop = thirdLoop + 1
                           End If
                          Loop


                    End If

                    SecondLoop = SecondLoop + 1

                    End If
                MainLoop = MainLoop + 20

                Loop

                Worksheets("Final").Activate

End Sub

Aucun commentaire:

Enregistrer un commentaire