The goal of this post is to at least properly convey what I want to do to avoid driving a certain clog wearing Office guru nuts
I have an inventory spreadsheet that tracks rack locations. The system works like this: fork truck drivers move pallets of parts in and out of a rack. They write down on a log sheet 5 pieces of info: part number, quantity, rack location, date, and whether it is going into the rack, or out. At the end of their shift, they end up with a list of rack moves both IN and OUT. A data entry person enters the list into a spreadsheet, into the "Log_Sheet_Entry" sheet. The info she enters go into the columns "Product", "IN or OUT", "Date", "Quantity", "Rack Location". That's where it stops, for now.
What then needs to be done is updating of the inventory sheet in the spreadsheet. The inventory sheet has the same columns, with the exception of "IN or OUT" since it only tracks what is currently IN the racks. This then gets handed to the fork truck drivers so they can find stuff in lieu of a barcode system (for now).
What I want to be able to do next is the tricky part. I want to automatically update the inventory based on the "Log_Sheet_Entry" line items. But it's not completely straight forward since there are different scenarios that I need to be prepared for. Here is the logic I've come up with so far (psuedo code):
Code: Select all
SORT LOG_SHEET_ENTRY DATA BY DATE, THEN SORT SO THAT "OUT" IS THE FIRST STATUS ENCOUNTERED FOR EACH DATE
FOR LOOP FROM LOG_SHEET_ENTRY ROW = 2 TO LASTROW IN LOG_SHEET_ENTRY
RACK = LOG_SHEET_ENTRY.ROW.RACK VALUE 'E.G. CX1
SEARCH INVENTORY FOR RACK
IF INVRACK MATCH IS FOUND
IF STATE = "IN"
IF ROW.PRODUCT = INVPRODUCT
IF INVQTY = QUANTITY THEN
IGNORE QTY
INVDATE.VALUE = DATE 'INSERT UPDATED DATE
ELSE
UPDATE INVQTY = QUANTITY
INVDATE = DATE
END IF
LOG_SHEET_ENTRY.ROW.DELETE
EXIT SUB
ELSE
INVPRODUCT = ROW.PRODUCT
INVQTY = ROW.QUANTITY
INVDATE = DATE
LOG_SHEET_ENTRY.ROW.DELETE
EXIT SUB
END IF
ROW.ADD PRODUCT, QUANTITY, DATE
ELSE
LOG_SHEET_ENTRY.ROW.DEL
ELSE
INVENTORY.ROW.ADD RACK, PRODUCT, QUANTITY, DATE
END IF
NEXT ROW
So in the case that the rack exists in the inventory sheet and we are trying to add something, the first thing we need to do is see if it's the same Part Number. If it's the same part number, we need to check the Quantity. If the Quantity is different, we update the Quantity field and the Date and we go on to the next row of data in the Log_Sheet_Entry sheet. If it's a different Part Number, we basically just write over it with the next Part Number from the Log_Sheet_Entry data and move on to the next row. Actually that's all we do, maybe it's not that complicated after all
After each row, the row on Log_Sheet_Entry is deleted until we have completed the entire list, leaving us with nothing but the 1st row (column labels) intact.
This is what I've got so far by just messing around with the record macro function... which seems to add a lot of bloat to code since it's very specific in what it copies from you.
Code: Select all
Sub Update_Inventory()
'The point of this macro is to quickly update the list so that no one has
'to do the tedious task of updating the inventory list manually.
'log variables are from the log sheet list
Dim logRack As String
Dim logPart As String
Dim logDate As Date
Dim logQty As Integer
'Inv variables are from the Inventory sheet list
Dim InvRack As String
Dim InvPart As String
Dim InvDate As Date
Dim InvQty As Integer
Dim lRow As Integer
'Sort Log sheet based on Date, and then OUT, so that all parts are
'taken out of the system first to avoid taking out items that should
'actually be put in. for example, part was taken out and put back
'in after consuming a portion, but not all of it, within 1 day.
Range("A1:B4").Select
Range(Selection, Selection.End(xlDown)).Select
lRow = Range(Selection, Selection.End(xlDown)).Select 'Row count
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Worksheets("Log_Sheet_Entry").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Log_Sheet_Entry").Sort.SortFields.Add Key:=Range( _
"C2:C83"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Log_Sheet_Entry").Sort.SortFields.Add Key:=Range( _
"B2:B83"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Log_Sheet_Entry").Sort
.SetRange Range("A1:E83")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-15
Range("H3").Select
'Start going through the log sheet list to add and remove inventory items
Dim xRow As Integer
Dim lRow As Integer
'lRow = end of list
For xRow = 2 To lRow
'Find xRow.logRack in inventory sheet
Range("E2").Select
Selection.Copy
Sheets("Inventory").Select
Cells.Find(What:="CAL5", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Sheets("Log_Sheet_Entry").Select
Range("E3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Inventory").Select
Cells.Find(What:="FZ5", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Sheets("Log_Sheet_Entry").Select
Range("E4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Inventory").Select
Cells.Find(What:="CW4", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Range("C190:F190").Select
Range("F190").Activate
End SubSorry for the super long post, but hopefully everything is at least clear on what I'm trying to do!
-John
)