Hello. I know this topic has been discussed in other posts, but every time I try to modify the codes I cannot get it to work.
I have a supply order form where column D has the name of each item and columns H (used), I (expired), J (damaged), and L (new) have the numbers that need to be ordered for that item.
My inventory sheet is laid out by location where each item is kept, but a single item type might be kept in multiple locations. I have a code that looks through the locations and when it finds something needs to be ordered, it populates my order form with the correct number in the correct column.
For example... I have item "x" is kept in 2 different locations.
Location #1 has 5 of this item, and 1 is used, leaving only 4. The code reads 1 missing, adds "Item X" in column D of the order form and a "1" in column H (used).
Location #2 also has 5 of this item, but 1 is used and 2 are expiring. The code will add this to a different row on the order form, again adding "Item X" in column D of the order form and a "1" in column H (used), and a "2" in column I (expired).
I need the code to consolidate the duplicate items in column D, and add the totals for each column (one row for item X with a "2" in column H (used) and a "2" in column I (expired).
Ideally, this would be checked each time the code goes to add an item to the order form, that way duplicate rows won't have to be deleted.
As a curve ball, the length of the order form may change. Currently, the row range is 24 to 53 by default. But if more rows are needed, then the code automatically adds the rows and inserts the data. See below for my "Fill Order" code (maybe it'll help you understand what it's doing)...
If this "duplication check" has to be completed once the below code has run, then fine, but that seems like it would be long and drawn out. Ideally, the search and sums would happen while the code below is running, but it's not a "make-or-break" thing for me.
Any help is GREATLY appreciated!
And thanks in advance!
Using Excel 2016
I have a supply order form where column D has the name of each item and columns H (used), I (expired), J (damaged), and L (new) have the numbers that need to be ordered for that item.
My inventory sheet is laid out by location where each item is kept, but a single item type might be kept in multiple locations. I have a code that looks through the locations and when it finds something needs to be ordered, it populates my order form with the correct number in the correct column.
For example... I have item "x" is kept in 2 different locations.
Location #1 has 5 of this item, and 1 is used, leaving only 4. The code reads 1 missing, adds "Item X" in column D of the order form and a "1" in column H (used).
Location #2 also has 5 of this item, but 1 is used and 2 are expiring. The code will add this to a different row on the order form, again adding "Item X" in column D of the order form and a "1" in column H (used), and a "2" in column I (expired).
I need the code to consolidate the duplicate items in column D, and add the totals for each column (one row for item X with a "2" in column H (used) and a "2" in column I (expired).
Ideally, this would be checked each time the code goes to add an item to the order form, that way duplicate rows won't have to be deleted.
As a curve ball, the length of the order form may change. Currently, the row range is 24 to 53 by default. But if more rows are needed, then the code automatically adds the rows and inserts the data. See below for my "Fill Order" code (maybe it'll help you understand what it's doing)...
If this "duplication check" has to be completed once the below code has run, then fine, but that seems like it would be long and drawn out. Ideally, the search and sums would happen while the code below is running, but it's not a "make-or-break" thing for me.
Any help is GREATLY appreciated!
And thanks in advance!
Using Excel 2016
Code:
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
Application.ScreenUpdating = False
Number_1 = 1
Number_2 = Cells(6, 4)
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
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
Application.ScreenUpdating = True
End Sub
Last edited: