VBA - How to complete one loop before beginning another

[AcornNut]

I have a code that searches Sheet1 ("Location") for certain criteria and populates Sheet2 ("Supply Usage Form") with that data. This works fine, after looping through all the rows on Sheet1, I need the code to loop through all the newly added data in Sheet2 for duplicates, combine the rows and sum the values for each identical item. The first loop works perfectly on its own.

The problem is... if Sheet2 is blank, then neither code works and it continually compiles and never completes. If there's data already in Sheet2, then when the code is run it repeats the first loop, but doesn't combine anything, so I have duplicates, but when you run it again the second loop will combine and sum the values, but moves the original data down, leaving a number of blank rows between the newly combined rows and the existing data. And continues to do so indefinitely with each new run of the code.

What I need is, whether or not Sheet2 is blank or has data already, the code to add the data from Sheet1 and the check Sheet2 for duplicates and, if it finds any, combine the rows and sum the values in the appropriate columns, then clear the original data.

Any help is greatly appreciated! Thank you in advance!!!

Sub fillorder1()
   Dim finalrow As Long
   Dim i As Integer
   Dim ExpDate As Date
   Dim lastrow As Long, NextRow As Long
   Dim Flg As Boolean
   Dim LR As Long
   Application.ScreenUpdating = False
   
    Number_1 = 1
    Number_2 = Cells(6, 4)
    LR = Sheets("Supply Usage Form").Range("B24").End(xlDown).Row
    NextRow = 54
    lastrow = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row + 1
    finalrow = Sheets("Location").Range("B9").End(xlDown).Row
    ExpDate = Date + 30
        
    Flg = True
    For i = 9 To finalrow
        If Flg And lastrow > 53 Then
            Rows(NextRow).Insert
            Rows(53).Copy
            Rows(NextRow).PasteSpecial xlFormats
            lastrow = NextRow
            NextRow = NextRow + 1
        End If
        Flg = False
        If (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) = "") Then
            Sheets("Location").Cells(i, 1).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 2).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 12).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
            lastrow = lastrow + 1
            Flg = True
        ElseIf (Sheets("Location").Cells(i, 12) = "") And (Sheets("Location").Cells(i, 13) <> "") Then
            Sheets("Location").Cells(i, 1).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 2).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 13).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
            lastrow = lastrow + 1
            Flg = True
        ElseIf (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) <> "") Then
            Sheets("Location").Cells(i, 1).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 2).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 12).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 13).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
            lastrow = lastrow + 1
            Flg = True
        End If
    Next i


        
Flg = True
For x = LR To 24 Step -1
    For y = 24 To LR
        If Sheets("Supply Usage Form").Cells(x, 2).Value = Sheets("Supply Usage Form").Cells(y, 2).Value And Sheets("Supply Usage Form").Cells(x, 3).Value = Sheets("Supply Usage Form").Cells(y, 3).Value And x > y Then
           Sheets("Supply Usage Form").Cells(y, 8).Value = Sheets("Supply Usage Form").Cells(x, 8).Value + Cells(y, 8).Value
           Sheets("Supply Usage Form").Cells(y, 9).Value = Sheets("Supply Usage Form").Cells(x, 9).Value + Cells(y, 9).Value
           Sheets("Supply Usage Form").Cells(y, 10).Value = Sheets("Supply Usage Form").Cells(x, 10).Value + Cells(y, 10).Value
           Sheets("Supply Usage Form").Cells(y, 12).Value = Sheets("Supply Usage Form").Cells(x, 12).Value + Cells(y, 12).Value
           Rows(x).EntireRow.ClearContents
        Exit For
        End If
    Next y
Next x
Flg = True


    lastrow = Sheets("Supply Usage Form").Range("B24").End(xlDown).Row + 1
        If Sheets("Supply Usage Form").Range("B" & lastrow).Row > 53 Then
            Rows(lastrow).Delete
        End If


        
    If Range("C9") = "" Then
    UserForm3.Show
    End If
    
    If Range("F9") = "" Then
    UserForm4.Show
    End If
   Sheets("Supply Usage Form").Cells(6, 4).Value = Number_2 + Number_1
   
   Application.ScreenUpdating = True
End Sub

 

[StephenCrump]

One immediate problem is this:

LR = Sheets("Supply Usage Form").Range("B24").End(xlDown).Row

....

For x = LR To 24 Step -1
    For y = 24 To LR
    ....

If Sheets("Supply Usage Form") is blank, then LR will be 1,048,576 (unless you have an old version of Excel). Perhaps a quick check might be in order before you launch into a potential 1,000 billion iterations?

It sounds like you have other issues with the code even if Sheets("Supply Usage Form") is not blank.

Perhaps you could explain what you are trying to do, as it's not immediately clear from the code.

 

[AcornNut]

Thank you for the reply, StephenCrump!

I have multiple locations that I need items kept and the inventory form is organized by the location of each item. So I may have "Item X" in 4 different locations and "Item Y" in 2 locations. What I'm trying to do is have the code run through the inventory sheet called "Location." It loops through each row in "Location" looking in Columns 12 and 13 for any numbers (items missing or expiring) and then auto-populates the requisition form "Supply Usage Form") with the name of the item (offset (0, 2)), the order code (offset(, 0)), and whether it's missing (offset (0, 5)) or expired (offset (0, 6)). The default range on the order form is row 24 to row 53. The code adds more rows if needed. This code (the first For Next loop) works perfectly by itself. The problem is that, because an item may be in more than one location, the code populates the same item multiple times, taking up space and making it easier for the logistics people to miss an item or send too many.

What I want the code to do is to have only one line for each item on the order form ("Supply Usage Form"), but sum the totals needed. Ideally, what I'd like the code to do is, as it discovers items missing or expiring on the "Locations" sheet, look to see if that item is already in the range on the "Supply Usage Form" (Row 24 to the last row with data - which may vary), and if so, only sum the totals in the corresponding columns (used, expiring). Otherwise, add a new line item and add the data. I don't have a clue of even how to begin that, so I was having it run through the first loop, simply checking for missing and expired items and populating the order form. THEN, look through the order form and consolidate each item so there's no duplicates and sum the used and expiring columns for that item.

I hope that helps!!! Please forgive the horrible coding. I'm not trained in this AT ALL and have been piecing codes as I go.
Again, thanks for the reply!!!!!! Any help is GREATLY appreciated.

 

[StephenCrump]

Sorry for the delay in responding.

Let me paraphrase and use a hypothetical example to make sure i understand you correctly:

The inventory sheet has three lines for item ABC:
- Location A has 3 missing and 2 expired
- Location B has nil missing and nil expired
- Location C has nil missing and 4 expired

Therefore you want the order sheet to be populated with a single line to order 9 units of ABC - 3 missing, and 6 expired?

My guess is that the order sheet is driven entirely by the inventory sheet .. there are no pre-existing order lines for ABC that we need to watch out for?
If so, we can write code to:

- loop through the inventory sheet finding missing or expired items
- add up all subsequent rows for that item
- write the results once for each item to the order sheet.

We can use a collection or dictionary object to test that we haven't already calculated the order for any particular item.

Am I on the right track?