Visual Basic program for updating exit-factory dates on a set of Excel-exported purchase orders from an enterprise resource planning system
Christopher Clayton
07/01/2015
WORKBOOK SHEET 1
This is the Excel file import display, for an order number exported from an enterprise resource planning system. The program involves column indices used for the ERP system paired with this instance of the program, such as the item code, description, and exit-factory date column indices.
WORKBOOK SHEET 2
This contains all user-defined parameters such as which file-name (must be open in the same instance of Excel) to draw exit-factory data from, the exit-factory date columns to check in that workbook, and the cut-off exit-factory date - below which that line gets added to a report about over-due items on a different sheet.
WORKBOOK SHEET 3
This is an optional sheet to re-formulate data from sheet 1 into a format for, say, global subsidiaries or customers to read and involves only what they want to know about, namely # of units left on balance and # of units that are left.
WORKBOOK SHEET 4
This is the list of order numbers to import, update, and subsequently export in succession. Due to the way the ERP system works (export a list of Excel files for import, then they get re-exported to the server and picked up by the server for export into the ERP system), this checks row 2 and ends when row 2 has a blank entry in column 1. When a record gets imported, column 2 is deleted, so the old column 3 becomes a new column 2. This was done because of the quirks of how the import algorithm for this particular ERP system was created, i.e. there were multiple sub-routine calls within the import method and the program to call it multiple times for each order number was not working multiple times unless it was constructed in this manner.
WORKBOOK SHEET 5
Due to the way sublines worked upon import of an Excel sheet exported from this particular ERP system, the sublines are displayed in Excel with no item code or description and list the same line number as the original line. There is also a risk of exporting the file and having the sub-lines deleted upon the server's exporting the data into the ERP system. Therefore, any Excel rows with blank item code columns are deleted and included in this sheet.
WORKBOOK SHEET 6
In order to coordinate possible issues with electronic data interchange issues with a particular subsidiary, this is a report of system purchase order numbers that also have a customer order number assigned to them (originated from an EDI transmission from that subsidiary). This is used to check in with the local IT employee that the exit-factory changes were actually noted by the transmission system (SOAP or whatever else sort of protocal) and actually transmitted to the subsidiary's system.
WORKBOOK SHEET 7
Any purchase order Excel lines updated to a date below a certain cut-off (user-defined paramter) are deemed over-due and added to this report, to check with the factory or other type of vendor about it.
WORKBOOK SHEET 8
Any order number and item code combinations not found on the vendor report are added here, to follow up with the vendor about it.
UPDATE EX-FACTORY ALGORITHM
Sub updateExFact()
Dim fileNameMatch As String
Dim exitFactoryMatchColumn As Integer
Dim itemMatchColumn As Integer
'Dim POMatchColumn As Integer
Dim quantColumn As Integer
'Dim lastColumn As Integer
Dim PONumber As String
Dim POCheckColumn As Integer
Dim fileNameMatchSheetIndex As Integer
Dim revisedDateColumn As Integer
Dim currErrorRow As Integer
Dim currChangedCustNumberRow As Integer
Dim Match As Boolean
Dim currLineNumberDelete As Integer
Dim currDeleteLineRowCheck As Integer
Dim workbookMatchRow As Integer
Dim currPORow As Integer
Dim pastDueCheckRow As Integer
Dim pastDueCurrReportRow As Integer
Dim pastDueDate As Date
Dim currMissingItemCodeLogRow As Integer
fileNameMatch = ThisWorkbook.Sheets(2).Cells(2, 1)
'POMatchColumn = ThisWorkbook.Sheets(2).Cells(2, 2)
itemMatchColumn = ThisWorkbook.Sheets(2).Cells(2, 3)
exitFactoryMatchColumn = ThisWorkbook.Sheets(2).Cells(2, 4)
quantColumn = ThisWorkbook.Sheets(2).Cells(2, 5)
workbookMatchRow = ThisWorkbook.Sheets(2).Cells(2, 7)
PONumber = ThisWorkbook.Sheets(1).Cells(2, 1)
POCheckColumn = ThisWorkbook.Sheets(2).Cells(2, 10)
fileNameMatchSheetIndex = ThisWorkbook.Sheets(2).Cells(2, 11)
revisedDateColumn = ThisWorkbook.Sheets(2).Cells(2, 6)
currPORow = 4
currDeleteLineRowCheck = 4
currLineNumberDelete = 0
currErrorRow = 2
currChangedCustNumberRow = 2
pastDueCurrReportRow = 2
pastDueCheckRow = 4
pastDueDate = ThisWorkbook.Sheets(2).Cells(2, 12)
currMissingItemCodeLogRow = 2
Match = False
'Find latest blank item code cells in issues/error report tabs
Do While ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 1) <> 0
currMissingItemCodeLogRow = currMissingItemCodeLogRow + 1
Loop
Do While ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 1) <> 0
pastDueCurrReportRow = pastDueCurrReportRow + 1
Loop
Do While ThisWorkbook.Sheets(5).Cells(currErrorRow, 1) <> 0
currErrorRow = currErrorRow + 1
Loop
Do While ThisWorkbook.Sheets(6).Cells(currChangedCustNumberRow, 1) <> 0
currChangedCustNumberRow = currChangedCustNumberRow + 1
Loop
'Main loop to update dates and report on any errors and/or changes to customer order numbers
Do While ThisWorkbook.Sheets(1).Cells(currPORow, 1) <> 0
Do While Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) <> 0
If ThisWorkbook.Sheets(1).Cells(currPORow, 2) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) <> 0 And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then 'Test if PO number, item code are a match and that there are positive units in delivery
'If ThisWorkbook.Sheets(1).Cells(currPORow, 2) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then 'Check if item code and order number are a match, regardless of whether or not anything is being delivered or not. Also, even if items are closed out on the factory side, on this side nothing may have been input as a receivable yet because it may still be in-transit without documents having been sent to or processed on this end.
Match = True
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, revisedDateColumn) <> 0 Then
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, revisedDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 9) = ThisWorkbook.Sheets(1).Cells(currPORow, 8)
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
If ThisWorkbook.Sheets(1).Cells(2, 13) <> 0 Then
ThisWorkbook.Sheets(6).Cells(currChangedCustNumberRow, 1) = ThisWorkbook.Sheets(1).Cells(2, 13)
ThisWorkbook.Sheets(6).Cells(currChangedCustNumberRow, 2) = ThisWorkbook.Sheets(1).Cells(currPORow, 1)
currChangedCustNumberRow = currChangedCustNumberRow + 1
End If
Else
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, exitFactoryMatchColumn)
'ThisWorkbook.Sheets(1).Cells(currPORow, 12).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 9) = ThisWorkbook.Sheets(1).Cells(currPORow, 8)
'ThisWorkbook.Sheets(1).Cells(currPORow, 10) = ThisWorkbook.Sheets(1).Cells(currPORow, 9) + 30
'ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
End If
'Else
'
' ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 1) = ThisWorkbook.Sheets(1).Cells(2, 1)
' ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 2) = ThisWorkbook.Sheets(1).Cells(currPORow, 1)
' ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 3) = ThisWorkbook.Sheets(1).Cells(currPORow, 2)
' ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 4) = ThisWorkbook.Sheets(1).Cells(currPORow, 4)
' ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 5) = ThisWorkbook.Sheets(1).Cells(2, 18)
' ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 6) = ThisWorkbook.Sheets(1).Cells(currPORow, 8)
' ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 7) = ThisWorkbook.Sheets(1).Cells(currPORow, 11)
' ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 8) = ThisWorkbook.Sheets(1).Cells(currPORow, 12)
'
' currMissingItemCodeLogRow = currMissingItemCodeLogRow + 1
End If
workbookMatchRow = workbookMatchRow + 1
Loop
If ThisWorkbook.Sheets(1).Cells(currPORow, 2) = 0 Then
ThisWorkbook.Sheets(5).Cells(currErrorRow, 1) = ThisWorkbook.Sheets(1).Cells(2, 1)
ThisWorkbook.Sheets(5).Cells(currErrorRow, 2) = ThisWorkbook.Sheets(1).Cells(currPORow, 1)
currErrorRow = currErrorRow + 1
currLineNumberDelete = ThisWorkbook.Sheets(1).Cells(currPORow, 1)
Do While ThisWorkbook.Sheets(1).Cells(currDeleteLineRowCheck, 1) <> 0
If ThisWorkbook.Sheets(1).Cells(currDeleteLineRowCheck, 1) = currLineNumberDelete Then
ThisWorkbook.Sheets(1).Rows(currDeleteLineRowCheck).Delete
Else
currDeleteLineRowCheck = currDeleteLineRowCheck + 1
End If
Loop
currDeleteLineRowCheck = 4
End If
If Match = False Then 'Report items not on the report at all
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 1) = ThisWorkbook.Sheets(1).Cells(2, 1)
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 2) = ThisWorkbook.Sheets(1).Cells(currPORow, 1)
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 3) = ThisWorkbook.Sheets(1).Cells(currPORow, 2)
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 4) = ThisWorkbook.Sheets(1).Cells(currPORow, 4)
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 5) = ThisWorkbook.Sheets(1).Cells(2, 18)
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 6) = ThisWorkbook.Sheets(1).Cells(currPORow, 8)
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 7) = ThisWorkbook.Sheets(1).Cells(currPORow, 11)
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 8) = ThisWorkbook.Sheets(1).Cells(currPORow, 12)
currMissingItemCodeLogRow = currMissingItemCodeLogRow + 1
End If
currPORow = currPORow + 1
workbookMatchRow = ThisWorkbook.Sheets(2).Cells(2, 7)
Match = False
Loop
'Check if any dates have not truly been updated, e.g. report open units left on items that don't meet the exit-factory cut-off date specified
Do While ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 1) <> 0
If ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 8) < pastDueDate Or ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 8) = pastDueDate Then
ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 1) = ThisWorkbook.Sheets(1).Cells(2, 1)
ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 2) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 1)
ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 3) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 2)
ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 4) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 4)
ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 5) = ThisWorkbook.Sheets(1).Cells(2, 18)
ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 6) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 8)
ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 7) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 11)
ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 8) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 12)
pastDueCheckRow = pastDueCheckRow + 1
pastDueCurrReportRow = pastDueCurrReportRow + 1
Else
pastDueCheckRow = pastDueCheckRow + 1
End If
Loop
End Sub
\*PROGRAM TO EXTRACT KEY DATA FROM SHEET 1
Sub extract_data()
Dim currPONameRow As Integer
Dim currToRow As Integer
Dim currPORow As Integer
Dim username As String
currPORow = 4
currToRow = 2
currPONameRow = 2
If Sheets(3).Cells(currToRow, 1) <> 0 Then
Do Until Sheets(3).Cells(currToRow, 1) = 0 'start at first blank output cell to copy data to (so one can import more files and have them matched with the other file
currToRow = currToRow + 1
Loop
End If
Do While Sheets(1).Cells(currPONameRow, 1) <> 0
Sheets(3).Cells(currToRow, 1) = Sheets(1).Cells(2, 1) 'list PO number
Sheets(3).Cells(currToRow, 2) = Sheets(1).Cells(2, 18)
Sheets(3).Cells(currToRow, 3) = Sheets(1).Cells(currPORow, 1) 'list line number
Sheets(3).Cells(currToRow, 4) = Sheets(1).Cells(currPORow, 2) 'list item number
Sheets(3).Cells(currToRow, 5) = Sheets(1).Cells(currPORow, 4) 'item desc
Sheets(3).Cells(currToRow, 6) = Sheets(1).Cells(currPORow, 11) 'total ordered
Sheets(3).Cells(currToRow, 7) = Sheets(1).Cells(currPORow, 12) 'total remaining
Sheets(3).Cells(currToRow, 11) = Sheets(1).Cells(currPORow, 13) 'price for any invoice analysis needed (if the Access database is down, etc.)
Sheets(3).Cells(currToRow, 9) = Sheets(1).Cells(currPORow, 8) 'extract exit-factory date (after updating it)
Sheets(3).Cells(currToRow, 13) = Sheets(1).Cells(currPORow, 11) - Sheets(1).Cells(currPORow, 12) 'total received
If Sheets(3).Cells(currToRow, 7) = 0 Then
Sheets(3).Cells(currToRow, 8) = "True"
Else
Sheets(3).Cells(currToRow, 8) = "False"
End If
If Sheets(3).Cells(currToRow, 3) = 0 Then
Sheets(3).Rows(currToRow).Delete
End
End If
currPORow = currPORow + 1
currToRow = currToRow + 1
Loop
End Sub
Accounting for sub-lines - complete script updated in 2019
This version accounts for adding and deleting sub-lines against an item code's primary line to match with an update source (e.g. an updated supplier list). This is still in the context of an ERP system where the sub-line item code cells are blank in the Excel export.
To account for multiple edge-cases and branching conditionals, there is a lot of repetition here. Some of it could have been put into a different method and instantiated with a function call, but I was not too concerned with that for a Visual Basic program.
Sub updateExFact()
Dim fileNameMatch As String
Dim exitFactoryMatchColumn As Integer
Dim itemMatchColumn As Integer
'Dim POMatchColumn As Integer
Dim quantColumn As Integer
'Dim lastColumn As Integer
Dim PONumber As String
Dim POCheckColumn As Integer
Dim fileNameMatchSheetIndex As Integer
Dim revisedDateColumn As Integer
Dim currErrorRow As Integer
Dim currChangedCustNumberRow As Integer
Dim openQtyColumn As Integer
Dim actualExfactColumn As Integer
Dim KTCurrDateColumn As Integer
Dim KTFinalDateColumn As Integer
Dim KTCurrDateColumnRemaining As Integer
Dim currPOLineNumber As String
Dim currPOItemCode As String
Dim currSplitLine As Integer
Dim KTSplitQtySum As Integer
Dim currPOLineQty As Integer
Dim ItemMatch As Boolean
Dim ItemMatch2 As Boolean
Dim matchComplete As Boolean
Dim currLineNumberDelete As Integer
Dim currDeleteLineRowCheck As Integer
Dim workbookMatchRow As Long
Dim currPORow As Integer
Dim pastDueCheckRow As Integer
Dim pastDueCurrReportRow As Integer
Dim pastDueDate As Date
Dim currMissingItemCodeLogRow As Integer
Dim alreadyFoundMatch As Boolean
Dim currSublineSplitLogRow As Integer
Dim daysToArrival As Integer
Dim currTotalMismatchReportRow As Integer
Dim factoryTotalOnOrderCol As Integer
Dim itemCodeSum As Integer
Dim itemBalanceSum As Integer
Dim currSumRow As Integer
Dim currDateCheck As Date
Dim currPrimaryItemCode As String
Dim currPrimaryLineNumber As String
Dim currWorkbookRowTotalQuant As Integer
Dim currWorkbookRowQuantToDistribute As Integer
Dim alreadyMatched As Boolean
PONumber = ThisWorkbook.Sheets(1).Cells(2, 1)
fileNameMatch = ThisWorkbook.Sheets(2).Cells(2, 1)
'POMatchColumn = ThisWorkbook.Sheets(2).Cells(2, 2)
itemMatchColumn = ThisWorkbook.Sheets(2).Cells(2, 3)
exitFactoryMatchColumn = ThisWorkbook.Sheets(2).Cells(2, 4)
quantColumn = ThisWorkbook.Sheets(2).Cells(2, 5)
workbookMatchRow = ThisWorkbook.Sheets(2).Cells(2, 7)
POCheckColumn = ThisWorkbook.Sheets(2).Cells(2, 8)
fileNameMatchSheetIndex = ThisWorkbook.Sheets(2).Cells(2, 9)
revisedDateColumn = ThisWorkbook.Sheets(2).Cells(2, 6)
currPORow = 4
currLineNumberDelete = 0
currErrorRow = 2
currChangedCustNumberRow = 2
pastDueCurrReportRow = 2
pastDueCheckRow = 4
pastDueDate = ThisWorkbook.Sheets(2).Cells(2, 10)
currMissingItemCodeLogRow = 2
ItemMatch = False
ItemMatch2 = False
alreadyFoundMatch = False
alreadyFoundMatch2 = False
matchComplete = False
currSublineSplitLogRow = 2
daysToArrival = ThisWorkbook.Sheets(2).Cells(2, 11)
factoryTotalOnOrderCol = ThisWorkbook.Sheets(2).Cells(2, 12)
openQtyColumn = ThisWorkbook.Sheets(2).Cells(2, 13)
actualExfactColumn = ThisWorkbook.Sheets(2).Cells(2, 14)
itemCodeSum = 0
itemBalanceSum = 0
currSumRow = 0
currTotalMismatchReportRow = 2
KTCurrDateColumn = 28
KTFinalDateColumn = 43
'Find latest blank item code cells in issues/error report tabs. Pre-main-loop initialization
Do While ThisWorkbook.Sheets(10).Cells(currTotalMismatchReportRow, 1) <> 0
currTotalMismatchReportRow = currTotalMismatchReportRow + 1
Loop
Do While ThisWorkbook.Sheets(9).Cells(currSublineSplitLogRow, 1) <> 0
currSublineSplitLogRow = currSublineSplitLogRow + 1
Loop
Do While ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 1) <> 0
currMissingItemCodeLogRow = currMissingItemCodeLogRow + 1
Loop
Do While ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 1) <> 0
pastDueCurrReportRow = pastDueCurrReportRow + 1
Loop
'Do While ThisWorkbook.Sheets(5).Cells(currErrorRow, 1) <> 0
' currErrorRow = currErrorRow + 1
'Loop
Do While ThisWorkbook.Sheets(6).Cells(currChangedCustNumberRow, 1) <> 0
currChangedCustNumberRow = currChangedCustNumberRow + 1
Loop
'Main loop to update dates and report on any errors and/or changes to customer order numbers
Do While ThisWorkbook.Sheets(1).Cells(currPORow, 1) <> 0
Do While matchComplete = False And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) <> 0
'Actual main check to see if the item code exists on the report and what to do in different circumstances
'Test if PO number, item code are a match and that there are positive units in delivery
'If ThisWorkbook.Sheets(1).Cells(currPORow, 2) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) <> 0 And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber And alreadyFoundMatch = False Then
'Need to use this conditional instead now which does not care if a line is fully received or not, because of the way the primary line and sub-line additions are done below. If a primary line has 0 units left for receivables, this whole conditional can be skiped, or at least something like that happens. This also may account for some item codes ending up on the "not found" report when they are on the production report, but I'll have to run a test on that.
'Given that the first-party vendor is now handling "split lines" as new columns against the
'same row, the below number of conditionals greatly increased. However, they are also
'splitting new rows out when a particular date is fulfilled, and the new rows have 0 open
'quantity. So in the least, the True match needs to skip closed-out report rows entirely.
'However, because of the lag between the report closing out line items versus when the
'end-user receives the quantity onto a PO, an ElseIf should eventually be added in order to
'sum up how much has been closed out versus remaining balance quantity on the PO, and sort
'through segregating the total open PO quantity that will be closed out later onto its own
'lines if needed.
'Otherwise the first-party vendor would need to delay moving shipped quantity onto a 0 open
'qty row on the status report for at least 30 days to allow the end-user time to receive
'goods, either virtual in-transit or upon physical receipt
'Enter based on the first PO number and item code match, regardless of whether the vendor side's line is open or not
If ThisWorkbook.Sheets(1).Cells(currPORow, 2) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
ItemMatch = True
ItemMatch2 = True
alreadyFoundMatch = True
'Reference the ERP system's line number in case new split lines need to be created later
'However this is actually storing empty data because it prints as 000 to match the generic Special data formatting in that Excel column as a Double or Long, or pints blank if set as a String
'This is happening if copying between cells in column 2 except with one '0' for the regular General cell format type. So the cell value is not being stored whatsoever.
currPOLineNumber = ThisWorkbook.Sheets(1).Cells(currPORow, 1).Value
currPOItemCode = ThisWorkbook.Sheets(1).Cells(currPORow, 2).Value
'Situation to take into account running into a 1st-party manufacturer line item that is closed, and then subsequently
'running through all closed lines assuming all closed lines are in succession followed by a single open row
'This assumes running through all vendor closed row columns until the open one at the bottom of the list is hit
'To make it more robust, searching for another PO number and item code match has to be done
'Best way to do this is to keep checking if the next PO number and item code combination on the vendor report is a match, and this will terminate otherwise.
'However, the vendor report should already be sorted by item code and PO number, followed by 0 open quantity rows descending into open quantity rows
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 Then
Do While Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber
alreadyFoundMatch2 = False 'reinitialize availability of branch options for next round of the loop
'Termination for whenever on subsequent iterations, the Do While criteria is met but the next vendor row doesn't actually match for PO and item code.
'This means that the vendor report has no open quantity lines left for this item whatsoever because the below conditional branches would otherwise step into running the open vendor line update code if there were an open quantity row match
'Must fail and move on to finding a new match for the next PO line if either the PO number detected on the report or item code doesn't match anymore upon incrementing from the last-known matching 0 open quantity vendor report row without hitting an open quantity vendor row
'This means the vendor report does need to be sorted by PO number and item code, followed by 0 quantity open rows descending into open rows for each item code and PO combination
If currPOItemCode <> Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) <> PONumber Then
ItemMatch2 = False
matchComplete = True
'Loop through existing PO sub-lines that the code may still be on to get within 1 line of a new full PO line.
'The end of the primary outer loop already increments the row to the next full PO line, so we only look ahead to make sure that would actually happen for this test and clean up anything which would break this process
'Similarly for the case of open vendor side rows, these excess PO lines need to be closed out. Something would have had to go very wrong
'on the PO side for this to be required, such as many more lines being generated than necessary on that side.
'Would not be on a new PO line or other line's sub-line at this point to do this check, so any sub-lines detected upon look-ahead belong to the item code that matched with the workbook up until now and need to be cleaned up
Do While ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0
'Assign the last-known match as the date so it follows the pattern of descending increasing dates
'Using "actual ex-factory date" now for closed-out vendor lines because the "confirmed date" or "revised date" column as it used to be called may still list the overall worst-case ex-factory by the time it has left the factory.
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11) = currPOLineQty - ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12) = 0
currPORow = currPORow + 1
Loop
Exit Do
End If
currWorkbookRowQuantToDistribute = 0
currWorkbookRowTotalQuant = 0
alreadyFoundMatch2 = False
'Must Exit Do out of every branch of this series of conditionals because they all
'stand alone so exiting
'one conditional right into the next can cause an overlap where it might otherwise
'terminate and move on to the open vendor quantity PO loop properly. Exception is the
'very last conditional to tie up negative vendor row quantities
'However, Exit Do causes the outer loop to throw as well which skips analyzing anything else
'So some sort of overlap between the conditionals needs to be solved
'A boolean for Already Analyzed should cause later conditionals to skip that would overlap
'No update needed on this PO line if it is already equal to the total ordered on the vendor report which is closed out.
'However the PO line still needs to be removed from consideration for updating later even if it's open because it will be closed out later
If alreadyFoundMatch2 = False And ThisWorkbook.Sheets(1).Cells(currPORow, 11) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, 10) Then
'Assign the date of the closed workbook line just in case
'Using "actual ex-factory date" now for closed-out vendor lines because the "confirmed date" or "revised date" column as it used to be called may still list the overall worst-case ex-factory by the time it has left the factory.
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
workbookMatchRow = workbookMatchRow + 1
'If there is an existing PO subline and there's still a non-open vendor report line to check through, increment to the next PO sub-line.
'Otherwise if there is still a closed vendor report line to check but no additional PO sub-line exists, insert a new row to prime the next check so it can be filled in
'Must check for a continuing PO number and item match at this point since we have incremented to a new vendor row
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
currPORow = currPORow + 1
alreadyFoundMatch2 = True
'Set up a bare minimum new sub-line to be filled later if there is going to be another closed vendor report line to check
'Fill in a placeholder total qty and full balance
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) <> 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = 1
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 1
alreadyFoundMatch2 = True
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) > 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) <> 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
'If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) <> 0 Then
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = 1
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 1
' Exit Do
'End If
Exit Do
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) > 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
'An additional sub-line does exist for the open vendor row to update into, so no need to make a new PO line
'Must increment to that next row to set up the open vendor line update situation
currPORow = currPORow + 1
Exit Do
ElseIf currPOItemCode <> Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) <> PONumber Then
ItemMatch2 = False
matchComplete = True
'Close excess PO-side sub-lines if they exist, because an open vendor row was not found to continue the match check
'so long as we have not reached the complete end of the PO (line number row must still be non-zero)
Do While ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 1) <> 0
'Assign the last-known match as the date so it follows the pattern of descending increasing dates
'Using "actual ex-factory date" now for closed-out vendor lines because the "confirmed date" or "revised date" column as it used to be called may still list the overall worst-case ex-factory by the time it has left the factory.
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11) = currPOLineQty - ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12) = 0
currPORow = currPORow + 1
Loop
Exit Do
End If
End If 'End for no update needed between a closed vendor line and a PO line
'Update line to fully match the closed vendor report line if this line doesn't match in total quantity
'Balance cannot be closed to 0
If alreadyFoundMatch2 = False And ThisWorkbook.Sheets(1).Cells(currPORow, 11) <> Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, 10) And ThisWorkbook.Sheets(1).Cells(currPORow, 11) - ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 12) <> 0 Then
'Assign the date of the closed workbook line as well as quantity
'Using "actual ex-factory date" now for closed-out vendor lines because the "confirmed date" or "revised date" column as it used to be called may still list the overall worst-case ex-factory by the time it has left the factory.
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
workbookMatchRow = workbookMatchRow + 1
'Must still have a matching PO number and item code to proceed with these options since we have incremented to a new workbookMatchRow
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
currPORow = currPORow + 1
'Set up a bare minimum new sub-line to be filled later if there is going to be another closed vendor report line to check
'Fill in a placeholder total qty and full balance
alreadyFoundMatch2 = True
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) <> 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = 1
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 1
alreadyFoundMatch2 = True
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) > 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) <> 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
'Set up another sub-line if one doesn't exist for the open vendor line check process
'If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) <> 0 Then
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = 1
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 1
' Exit Do
'End If
Exit Do
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) > 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
'An additional sub-line does exist for the open vendor row to update into, so no need to make a new PO line
'Must increment to the next PO row to set up the open vendor check process
currPORow = currPORow + 1
Exit Do
ElseIf currPOItemCode <> Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) <> PONumber Then
ItemMatch2 = False
matchComplete = True
'Close excess PO-side sub-lines if they exist, because an open vendor row was not found to continue the match check
'so long as we have not reached the complete end of the PO (line number row must still be non-zero)
Do While ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 1) <> 0
'Assign the last-known match as the date so it follows the pattern of descending increasing dates
'Using "actual ex-factory date" now for closed-out vendor lines because the "confirmed date" or "revised date" column as it used to be called may still list the overall worst-case ex-factory by the time it has left the factory.
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11) = currPOLineQty - ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12) = 0
currPORow = currPORow + 1
Loop
Exit Do
End If
End If 'End for fully updating a PO line to match a closed vendor report line in complete
'Update line to fully match the closed vendor report line if this line doesn't match in total quantity, and if the balance isn't full but that the balance wouldn't go negative if the difference between the current total and replacement total were subtracted
'The problem without an additional check to catch 0 open balance is that a positive balance could be added to a line that's already closed, which we cannot do either
If alreadyFoundMatch2 = False And ThisWorkbook.Sheets(1).Cells(currPORow, 11) <> Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, 10) And (ThisWorkbook.Sheets(1).Cells(currPORow, 12) - (ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn)) > 0 Or ThisWorkbook.Sheets(1).Cells(currPORow, 12) - (ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn)) = 0) And ThisWorkbook.Sheets(1).Cells(currPORow, 12) <> 0 Then
'Assign the date of the closed workbook line as well as quantity
'Using "actual ex-factory date" now for closed-out vendor lines because the "confirmed date" or "revised date" column as it used to be called may still list the overall worst-case ex-factory by the time it has left the factory.
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow, 12)
'Update balance quantity first before the previous total quanitty is over-written
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = currPOLineQty - (ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn))
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn)
workbookMatchRow = workbookMatchRow + 1
'Must still have a matching PO number and item code to proceed with any of these branches since we have incremented to the next workbook row
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
currPORow = currPORow + 1
'Set up a bare minimum new sub-line to be filled later if there is going to be another closed vendor report line to check without another PO-side sub line
'Fill in a placeholder total qty and full balance
alreadyFoundMatch2 = True
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) <> 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = 1
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 1
alreadyFoundMatch2 = True
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) > 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) <> 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
'Set up another sub-line if one doesn't exist for the open vendor line check process
'If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) <> 0 Then
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = 1
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 1
' Exit Do
'End If
Exit Do
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) > 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
'An additional sub-line does exist for the open vendor row to update into, so no need to make a new PO line
'Must increment to the next PO row to set up the open vendor row check process
currPORow = currPORow + 1
Exit Do
ElseIf currPOItemCode <> Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) <> PONumber Then
ItemMatch2 = False
matchComplete = True
'Close excess PO-side sub-lines if they exist, because an open vendor row was not found to continue the match check
'so long as we have not reached the complete end of the PO (line number row must still be non-zero)
Do While ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 1) <> 0
'Assign the last-known match as the date so it follows the pattern of descending increasing dates
'Using "actual ex-factory date" now for closed-out vendor lines because the "confirmed date" or "revised date" column as it used to be called may still list the overall worst-case ex-factory by the time it has left the factory.
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11) = currPOLineQty - ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12) = 0
currPORow = currPORow + 1
Loop
Exit Do
End If
End If
'Need to move test for zero balance situations to its own place because it's causing too many problems
'If there is another PO row to move to, need to move there. But, testing for whether there is another matching closed vendor row that needs to be moved to is another matter.
'It could be the case that we are on the final vendor row but not the final PO sub-line, so in that case we need to stay on the current vendor row in order to use it to update the next open PO line or to create a new line
'The whole point of this conditional is to get to a point where we can move on and re-initialize to make an actual update
If alreadyFoundMatch2 = False And ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 0 Then
alreadyFoundMatch2 = True
'Special condition if the balance was 0 to begin with. Immediately transfer the entire difference between the vendor line and PO line to a new variable, and then make the current vendor line equal to the current PO line
If ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 0 Then
currWorkbookRowTotalQuant = ThisWorkbook.Sheets(1).Cells(currPORow, 11)
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
'There could a situation where another PO split line continues from here, but this is the last vendor row available.
'In that case, need to stay on the current vendor line but immediately move to the next PO sub-line and cannot modify the existing vendor PO row whatsoever
If currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow + 1, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow + 1, POCheckColumn) = PONumber Then
currWorkbookRowQuantToDistribute = (Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) - ThisWorkbook.Sheets(1).Cells(currPORow, 11))
'?????Need to set this to the negative of the difference in this case, if there is another vendor row that can take it.
Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = currWorkbookRowTotalQuant
'Must add this one instead of subtracting because the quantity being passed down is 0 or negative at this point
Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow + 1, quantColumn) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow + 1, quantColumn) + currWorkbookRowQuantToDistribute
workbookMatchRow = workbookMatchRow + 1
End If
'For the below options, we are moving out of a close PO line and per above, we will stay on a matching vendor row of 0 open qty no matter what. But I added the match test anyway to further ensure this.
'This will never invalidate still having a match to check so ItemMatch2 should not be set to False
'Move to a new open row if one is available and if we are still on a matching vendor row of 0 open quantity
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
currPORow = currPORow + 1
'Initialize a new minimum sub-line if one is not available and we are still on a match
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) <> 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = 1
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 1
End If
End If
End If
'Update PO line quantity to only match part of the closed vendor report line if doing a full quantity update would cause a negative balance if the difference between the current total and replacment total were subtracted
'If there is another vendor line that is closed, then pass the balance over to there so it can be used on the next pass.
'If there isn't another vendor line that has 0 open quantity, pass the balance
'Also need to alternatively enter here if the balance of the line is 0 (closed line)
If alreadyFoundMatch2 = False And ThisWorkbook.Sheets(1).Cells(currPORow, 11) <> Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, 10) And (ThisWorkbook.Sheets(1).Cells(currPORow, 12) - (ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn))) Then
currWorkbookRowTotalQuant = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn)
'Check to see what value it would take to ensure that if a value were added to the PO side, it wouldn't cause a negative balance. Keep track of all quantities that couldn't meet this criteria
'Situation that caused this branch to trigger is initially negative. So need to have an incrementing variable in the below loop to eventually get a solution, but the PO side cannot be directly manipulated in case there is a 0 balance
'In this case, the RowQuantToDistribute probably needs to be subtracted from the next row
'due to the way this formula requires a value to increase in order to find a solution to get
'out of it. So that means any decreases we are making to the vendor report
'have to simply be updated to be gone off the report entirely until the report
'is reloaded. Edits cannot be made to the PO side in a 0 balance situation, so
'this is the only real solution.
'The negative of RowQuantToDistribute does still need to be passed on to the next
'row though because we are saying that this is what already exists on the
'PO and is closed out. This could result in the next vendor PO row column
'going negative though, if receipts on the PO side are over-done or if the
'vendor reporting side is chopped up into further smaller quantities.
'Some checks to keep redistributing negatives needs to be done in that case.
'However, it's true that the same equation test has to be used because the goal is
'to get to a state of 0 balance quantity on the PO side rather than causing a negative
'balance. So it is a case of incrementing everything for the test to work, including
'on the vendor report side, and then it's passed on to the next vendor row that's
'closed out.
If ThisWorkbook.Sheets(1).Cells(currPORow, 12) > 0 Then
Do Until ThisWorkbook.Sheets(1).Cells(currPORow, 12) - (ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn)) = 0
currWorkbookRowTotalQuant = currWorkbookRowTotalQuant + 1
currWorkbookRowQuantToDistribute = currWorkbookRowQuantToDistribute + 1
Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = currWorkbookRowTotalQuant
Loop
End If
Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = currWorkbookRowTotalQuant
'Assign the date of the closed workbook line as well as quantity
'Using "actual ex-factory date" now for closed-out vendor lines because the "confirmed date" or "revised date" column as it used to be called may still list the overall worst-case ex-factory by the time it has left the factory.
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow, 12)
'Update balance qty first before current total qty is over-written
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = currPOLineQty - (ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn))
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow, 11)
'If currWorkbookRowTotalQuant was entirely closed out because of 0 balance on the PO side, then nothing will change in total quantity on the PO side
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = currWorkbookRowTotalQuant
'If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow + 1, quantColumn) > 0 Then
workbookMatchRow = workbookMatchRow + 1
'End If
'May need to short-circuit the below final part in favor of another conditional after this one if a zero or negative
'quantity was distributed to the next vendor line as a result. So it could bounce the code to an open vendor row without checking that if the
'workbookMatchRow is incremented here unconditionally
'Must make sure that this next row on the vendor report is still a PO number and item code match during this loop as well
If currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
'Add remaining to distribute onto this next vendor match row quantity cell directly so it will potentially align with proceeding PO-side lines
'Actually must be a negative add due to the way that currWorkbookRowQuantToDistribute has to be positively counted up in the difference equation. So it represents what has already been received into the PO side
'Negative actually cannot be passed down unless there is an item code match on the next row, so do not do it here
'Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) - currWorkbookRowQuantToDistribute
'Cannot move to the next currPORow here because a look-ahead check is already being done
'But, one needs to be done at the end as an Else clause
'currPORow = currPORow + 1
'Set up a bare minimum new sub-line to be filled later if there is going to be another closed vendor report line to check
'Fill in a placeholder total qty and full balance
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 Then
'ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) <> 0
Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) - currWorkbookRowQuantToDistribute
'If there is an upcoming vendor row that is open, then the remainder quantity to distribute has to be dumped onto a new PO sub-line.
'It then has to receive the date from one row back which was the last row known to be a closed vendor line
'The problem that could happen on subsequent attempts is if this split for the remainder already happened before.
'Actually must be a negative add due to the way that currWorkbookRowQuantToDistribute has to be positively counted up in the difference equation. So it represents what has already been received into the PO side
'Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) - currWorkbookRowQuantToDistribute
'Must make sure the next vendor row line was not zero'd out or negative ahead of time before creating a new palceholder sub-line
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = 0 Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) < 0 Then
Do While Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And (Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = 0 Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) < 0) And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber
currWorkbookRowQuantToDistribute = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn)
Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = 0
'Must add this one instead of subtracting because the quantity being passed down is 0 or negative at this point
Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow + 1, quantColumn) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow + 1, quantColumn) + currWorkbookRowQuantToDistribute
workbookMatchRow = workbookMatchRow + 1
'If we do run into the open vendor line for this PO number and item code combination,
'terminate by either setting up a new PO sub-line if there are no more or simply
'terminate while set on the current PO sub-line
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) > 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 2) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
'At this stage, the higher-level Do has to be terminated
'as well though so the code can proceed to the open vendor
'row situation
'Reminder - definition of a sub-line in this context is that column 2 is blank
If ThisWorkbook.Sheets(1).Cells(currPORow, 2) <> 0 Then
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = 1
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 1
Exit Do
End If
Exit Do
End If
'If we are on an open line but it is no longer a PO number or item code match, need to terminate as well as set ItemMatch to False.
If currPOItemCode <> Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) <> PONumber Then
ItemMatch2 = False
matchComplete = True
'Close excess PO-side sub-lines if they exist, because an open vendor row was not found to continue the match check
Do While ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0
'Assign the last-known match as the date so it follows the pattern of descending increasing dates
'Using "actual ex-factory date" now for closed-out vendor lines because the "confirmed date" or "revised date" column as it used to be called may still list the overall worst-case ex-factory by the time it has left the factory.
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11) = currPOLineQty - ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12) = 0
currPORow = currPORow + 1
Loop
Exit Do
End If
Loop
End If 'End for cycling newly-generated negative vendor report quantities to other vendor rows as necessary
'Test with the same conditions again and if it's still true, keep going. Otherwise this will move on without doing anything else
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) <> 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = 1
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 1
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
currPORow = currPORow + 1
End If
'Do not create a new line if one already exists, and if a vendor row line is still in existence then pass down the negative remainder from the previous situation
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
'Must make sure the next vendor row line was not zero'd out or negative ahead of time before creating a new palceholder sub-line
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = 0 Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) < 0 Then
Do While Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And (Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = 0 Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) < 0) And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber
currWorkbookRowQuantToDistribute = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn)
Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = 0
'Must add this one instead of subtracting because the quantity being passed down is 0 or negative at this point
Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow + 1, quantColumn) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow + 1, quantColumn) + currWorkbookRowQuantToDistribute
workbookMatchRow = workbookMatchRow + 1
'If we do run into the open vendor line for this PO number and item code combination,
'terminate by either setting up a new PO sub-line if there are no more or simply
'terminate while set on the current PO sub-line
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) > 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 2) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
'At this stage, the higher-level Do has to be terminated
'as well though so the code can proceed to the open vendor
'row situation
'Reminder - definition of a sub-line in this context is that column 2 is blank
If ThisWorkbook.Sheets(1).Cells(currPORow, 2) <> 0 Then
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = 1
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 1
Exit Do
End If
Exit Do
End If
'If we are on an open line but it is no longer a PO number or item code match, need to terminate as well as set ItemMatch to False.
If currPOItemCode <> Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) <> PONumber Then
ItemMatch2 = False
matchComplete = True
'Close excess PO-side sub-lines if they exist, because an open vendor row was not found to continue the match check
Do While ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0
'Assign the last-known match as the date so it follows the pattern of descending increasing dates
'Using "actual ex-factory date" now for closed-out vendor lines because the "confirmed date" or "revised date" column as it used to be called may still list the overall worst-case ex-factory by the time it has left the factory.
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11) = currPOLineQty - ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12) = 0
currPORow = currPORow + 1
Loop
Exit Do
End If
Loop
End If 'End for cycling newly-generated negative vendor report quantities to other vendor rows as necessary
'If the test condition still holds true after double-checking for no 0's or negatives, proceed. Otherwise this will stop and move on to the next PO line because the preceding conditional test made this invalid
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
currPORow = currPORow + 1
End If
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) > 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
'An additional sub-line does exist for the open vendor row to update into, so no need to make a new PO line
'Must increment to the next PO row to set up the open vendor row check process
currPORow = currPORow + 1
Exit Do
ElseIf currPOItemCode <> Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) <> PONumber Then
ItemMatch2 = False
matchComplete = True
'Close excess PO-side sub-lines if they exist, because an open vendor row was not found to continue the match check
'so long as we have not reached the complete end of the PO (line number row must still be non-zero)
Do While ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 1) <> 0
'Assign the last-known match as the date so it follows the pattern of descending increasing dates
'Using "actual ex-factory date" now for closed-out vendor lines because the "confirmed date" or "revised date" column as it used to be called may still list the overall worst-case ex-factory by the time it has left the factory.
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11) = currPOLineQty - ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12) = 0
currPORow = currPORow + 1
Loop
Exit Do
End If ' 1st end for special branching conditions from updating PO lines that can only take part of the quantity of a particular vendor row
ElseIf currPOItemCode <> Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) <> PONumber Then
ItemMatch2 = False
matchComplete = True
'Close excess PO-side sub-lines if they exist, because an open vendor row was not found to continue the match check
'so long as we have not reached the complete end of the PO (line number row must still be non-zero)
Do While ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 1) <> 0
'Assign the last-known match as the date so it follows the pattern of descending increasing dates
'Using "actual ex-factory date" now for closed-out vendor lines because the "confirmed date" or "revised date" column as it used to be called may still list the overall worst-case ex-factory by the time it has left the factory.
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11) = currPOLineQty - ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12) = 0
currPORow = currPORow + 1
Loop
Exit Do
End If ' 2nd end for special branching conditions from updating PO lines that can only take part of the quantity of a particular vendor row
End If 'Final end for updating PO lines that can only take a part of the quantity of a particular vendor row without incurring a negative balance quantity
'Conditional for if negative total quantity that was dumped from prior closed-out vendor
'rows resulted in the next row total quantity being negative.
'No negative quantity should have been passed if it was not a PO number and item code match per the above check upon incrementing to another workbookMatch Row
'It 's necessary to
'keep dumping quantity out to the next vendor report row and directly modify that report
'if updating a PO row wholesale would result in a negative balance, so long as there are further PO number and item code matches to do this.
'Also this stage needs to keep throwing negative quantity down to the next closed-out
'vendor row if one exists and if it is still negative in order to get through multiple
'cases of a vendor row inheriting negative quantity that causes a total negative.
'If the number of vendor rows that have 0 open quantity is depleted this way, then
'terminate and add a new placeholder sub-line on the PO side if there isn't another
'PO sub-line in existence to set up checking the open quantity vendor row
'Must be at the bottom of the conditional that may have caused negative vendor row total quantities to form so
'the outer loop can terminate if all closed vendor row situations have been checked as a result of this
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = 0 Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) < 0 Then
Do While Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 And (Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = 0 Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) < 0) And currPOItemCode = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber
currWorkbookRowQuantToDistribute = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn)
Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn) = 0
'Must add this one instead of subtracting because the quantity being passed down is 0 or negative at this point
Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow + 1, quantColumn) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow + 1, quantColumn) + currWorkbookRowQuantToDistribute
workbookMatchRow = workbookMatchRow + 1
'If we do run into the open vendor line for this PO number and item code combination,
'terminate by either setting up a new PO sub-line if there are no more or simply
'terminate while set on the current PO sub-line
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) > 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 2) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber Then
'At this stage, the higher-level Do has to be terminated
'as well though so the code can proceed to the open vendor
'row situation
'Reminder - definition of a sub-line in this context is that column 2 is blank
If ThisWorkbook.Sheets(1).Cells(currPORow, 2) <> 0 Then
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = 1
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 1
Exit Do
End If
Exit Do
End If
'If we are on an open line but it is no longer a PO number or item code match, need to terminate as well as set ItemMatch to False.
If currPOItemCode <> Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) <> PONumber Then
ItemMatch2 = False
matchComplete = True
'Close excess PO-side sub-lines if they exist, because an open vendor row was not found to continue the match check
Do While ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0
'Assign the last-known match as the date so it follows the pattern of descending increasing dates
'Using "actual ex-factory date" now for closed-out vendor lines because the "confirmed date" or "revised date" column as it used to be called may still list the overall worst-case ex-factory by the time it has left the factory.
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11) = currPOLineQty - ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12) = 0
currPORow = currPORow + 1
Loop
Exit Do
End If
Loop
End If 'End for cycling newly-generated negative vendor report quantities to other vendor rows as necessary
Loop 'End for closed vendor line situations
End If 'End for finding the first closed vendor line situation, move on to non-open lines
If currPOItemCode <> Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) <> PONumber Then
'Close out if we ran out of matches entirely at this point, and still have excess sub-lines. This can still happen even after the above conditional loops in cases such as there being more sub-lines on the PO line than vendor side, but that the PO side are all closed out already (0 balance left)
Do While ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 1) <> 0
'Assign the last-known match as the date so it follows the pattern of descending increasing dates
'Using "actual ex-factory date" now for closed-out vendor lines because the "confirmed date" or "revised date" column as it used to be called may still list the overall worst-case ex-factory by the time it has left the factory.
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, actualExfactColumn)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11) = currPOLineQty - ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12) = 0
currPORow = currPORow + 1
Loop
Exit Do
End If
'Do not enter this if the preceding conditional that looped through sorted closed vendor lines didn't find a matching PO number and item code combination
'(all vendor lines for that PO number and item code turned out to be closed in that case). Match is set to False in that case.
'The second item match variable must remain True to check open vendor PO lines.
'However, the second item match variable could be false due to never finding
'an open item code row after getting through all of the closed vendor rows.
'At the same time, the first match variable must remain true so the entire
'loop can terminate properly and move to the next currPORow
If ItemMatch2 = True And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) > 0 Then
'Enter this situation if sub-lines already exist on the PO side
'One exception not taken into account is if split lines are on the PO, but have been collapsed into even fewer splits on the manufacturer's report.
'The problem with sub-lines on the current manufacturer's report (what is now a number of new columns on the right-hand side rather than new rows
'is that there are a lot of column in the middle between the first and last columns which have to be removed. Then the last column could still
'be filled out without any others filled since it's the quantity column for the latest promise date on record. So if that is always filled out
'but under consideration in the macro loops, all cases where only a single PO line and single manufacturer offer date are under consideration
'will be passed up in favor of looking at the sub-line generation macros. However, the primary line alone should be updated with no sub-lines
'added anyway because it will not fulfill the conditions of the sub-line loops if it already hit the last manufacturer split date column with
'any positive quantity listed.
If ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 Then
'Add code here to keep checking the Kaitai right-hand date columns for the first cell which has a date in order to update the primary line with the first available date
'Do not re-initialize the date columns here. We may bypass this entire conditional if there is no split quantity to make, so they need to be initialized at the very beginning of the sub.
'KTCurrDateColumn = 28
'KTFinalDateColumn = 43
'Search for the first split quantity in the far-right manufacturer's report columns that doesn't contain 0 quantity
Do Until Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) <> 0
KTCurrDateColumn = KTCurrDateColumn + 1
Loop
'Initialize this for the PO versus report split line check that is set up for post analysis. currPORow will be constantly updating so this needs to be set as a reference back to the primary line
currSplitLine = currPORow
'Assign first date and first split quantity to the primary line item (or split line we are currently on after checking all vendor rows of 0 quantity, while still having a PO and item code match)
'if the balance would not go negative should the vendor-side PO quantity replace the current quantity for that line,
'and if the current balance isn't zero to begin with
If (ThisWorkbook.Sheets(1).Cells(currPORow, 12) - ((ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn))) > 0) And ThisWorkbook.Sheets(1).Cells(currPORow, 12) > 0 Then
'Clear ETD and ETA to let system calculate them. If they are present on split lines, the entire PO cannot be uploaded under all circumstances
ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(1, KTCurrDateColumn)
'Make total and balance equal if they were equal to begin with
If ThisWorkbook.Sheets(1).Cells(currPORow, 12) <> ThisWorkbook.Sheets(1).Cells(currPORow, 11) Then
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn)
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow, 12)
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = currPOLineQty - (ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn))
ElseIf ThisWorkbook.Sheets(1).Cells(currPORow, 12) = ThisWorkbook.Sheets(1).Cells(currPORow, 11) Then
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = ThisWorkbook.Sheets(1).Cells(currPORow, 11)
End If
'Need to check here if there even is another date to update at all before incrementing or it could be thrown off if there are spare PO-side split lines to close out
'May not actually be on a parent line here due to the 0 open quantity vendor row checks
KTCurrDateColumn = KTCurrDateColumn + 1
Do While KTCurrDateColumn < KTFinalDateColumn + 1
'Exit if there another positive split line quantity was found, or if we ran out of options
If KTCurrDateColumn = KTFinalDateColumn + 1 Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) > 0 Then
Exit Do
End If
KTCurrDateColumn = KTCurrDateColumn + 1
Loop
'Only increment to a new PO row at all if there is an actual PO-side split date requiring processing
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) <> 0 And KTCurrDateColumn < KTFinalDateColumn + 1 Then
currPORow = currPORow + 1
End If
'currPORow = currPORow + 1 'Now entered into the first split line
'Move on to the next split date. If there actually only was 1 date listed in the split columns, the split date conditionals should be short-circuited
'KTCurrDateColumn = KTCurrDateColumn + 1
'Otherwise straight close the parent line or split line that we are on after checking through vendor rows of 0 open quantity if we are somehow on a parent line that would go negative should the new
'PO vendor-side quantity be used as a replacement for total PO line quantity, or if it's closed already to begin with
ElseIf (ThisWorkbook.Sheets(1).Cells(currPORow, 12) - (ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn)) < 0) Or ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 0 Then
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow, 11)
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = currPOLineQty - ThisWorkbook.Sheets(1).Cells(currPORow, 12)
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 0
'Update date of closed line so it is potentially not out later than later lines
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(1, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
'Need to actually check here if there even is another date to update at all before incrementing or it could be thrown off if there are spare PO-side split lines to close out.
'We do know that there is still a split date to update since we are staying on the same one, so we can confidently move to the next PO row in this case
currPORow = currPORow + 1 'Now entered into the first split line if the primary line had to be closed out or was already closed out
End If
Do While KTCurrDateColumn < KTFinalDateColumn + 1
'Only go through conditionals if there is actual split quantity to update, and not at the end of potential dates to update yet
Do While Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) = 0 And KTCurrDateColumn < KTFinalDateColumn + 1
KTCurrDateColumn = KTCurrDateColumn + 1
Loop
If KTCurrDateColumn = KTFinalDateColumn + 1 Or KTCurrDateColumn > KTFinalDateColumn + 1 Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) = 0 Then
Exit Do
End If
'If existing split line is only partly received and would not negative if the full value were added, close out the current split line
'Otherwise go through creating a split line if we still have vendor open quantity to get through, but currently on a new PO line entirely
'This could go through a total check to re-distribute quantity on the vendor side like what was done above.
'However, I don't see a reason to go through all of that by this point. It's clearly an over-receiving error on the customer PO side for third-party
'receiving situations if comparing one single PO line versus one single third-party vendor line with no row quantity
'split-outs or right-hand column date split-outs. Unlike reconciling closed vendor lines which may be de-sync'd with
'customer-side PO lines, a receiving error is all that could reasonably happen at this point if a negative balance
'would occur.
'Or the third-party vendor split out extra rows for quantity still open on the vendor side, which means
'the vendor report needs to be re-formatted. Even if the third-party vendor partially ships something, they can keep
'the partial open on the same line and then a partial receipt on the PO side can be done without making it possible
'for a negative balance to occur should the vendor-side quantity replace the current PO quantity.
'So a simple close-out to reconcile it seems best, then any over-receipt reconciliation has to be done later because that's the only
'reason for that sort of line close-out to need to happen at this point.
''''''''
If ThisWorkbook.Sheets(1).Cells(currPORow, 2) = 0 And (ThisWorkbook.Sheets(1).Cells(currPORow, 12) - ((ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn))) > 0) And ThisWorkbook.Sheets(1).Cells(currPORow, 12) > 0 Then
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow, 12)
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = currPOLineQty - (ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn))
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(1, KTCurrDateColumn)
'Clear ETD and ETA to let system calculate them. If they are present on split lines, the entire PO cannot be uploaded under all circumstances
ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
KTCurrDateColumn = KTCurrDateColumn + 1
'Only go through conditionals for a reset if there is actual split quantity to continue updating, and not at the end of potential dates to update yet
Do While Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) = 0 And KTCurrDateColumn < KTFinalDateColumn + 1
KTCurrDateColumn = KTCurrDateColumn + 1
Loop
If KTCurrDateColumn = KTFinalDateColumn + 1 Or KTCurrDateColumn > KTFinalDateColumn + 1 Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) = 0 Then
Exit Do
End If
'Some check seems to be necessary here to see if another sub-line exsits after the current one or not
'Don't increment to a new row if there's no existing sub-line after it. Otherwise need to make a new subline in this branch
If ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 Then
'Need to check here if there even is another date to update at all before incrementing or it could be thrown off if there are spare PO-side split lines to close out
currPORow = currPORow + 1
Else
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
'Reinitialize current row to point to the new row addition which should be added directly below the current currPORow
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
'Update for both a split date update as well as a split quantity
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(1, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = 1
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 1
'currPORow = currPORow + 1
'KTCurrDateColumn = KTCurrDateColumn + 1
End If
ElseIf ThisWorkbook.Sheets(1).Cells(currPORow, 2) = 0 And ((ThisWorkbook.Sheets(1).Cells(currPORow, 12) - (ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn)) < 0) Or ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 0) Then
'Update date of closed split line so it is potentially not out later than later lines, and make sure the line is closed
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(1, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow, 11)
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = currPOLineQty - ThisWorkbook.Sheets(1).Cells(currPORow, 12)
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 0
KTCurrDateColumn = KTCurrDateColumn + 1
'Only go through conditionals for a reset if there is actual split quantity to continue updating, and not at the end of potential dates to update yet
Do While Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) = 0 And KTCurrDateColumn < KTFinalDateColumn + 1
KTCurrDateColumn = KTCurrDateColumn + 1
Loop
If KTCurrDateColumn = KTFinalDateColumn + 1 Or KTCurrDateColumn > KTFinalDateColumn + 1 Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) = 0 Then
Exit Do
End If
'Some check seems to be necessary here to see if another sub-line exists after the current one or not
'Don't increment to a new row if there's no existing sub-line after it. Otherwise need to make a new subline in this branch
If ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 Then
currPORow = currPORow + 1
Else
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
'Reinitialize current row to point to the new row addition which should be added directly below the current currPORow
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
'Update for both a split date update as well as a split quantity
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(1, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn)
'currPORow = currPORow + 1
KTCurrDateColumn = KTCurrDateColumn + 1
End If
'Set up a new sub-line if we reached the start of a new PO line already, but still have vendor row lines to process
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) <> 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 2) = 0 Then
'Update for both a split date update as well as a split quantity
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(1, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = ThisWorkbook.Sheets(1).Cells(currPORow, 11) 'Full balance for a new line
'Clear ETD and ETA to let system calculate them. If they are present on split lines, the entire PO cannot be uploaded under all circumstances
ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
currPORow = currPORow + 1
KTCurrDateColumn = KTCurrDateColumn + 1
'Need to set up a new placeholder sub-line immediately and find the next KT date cell with actual quantity in it or else it could move into another non-sub-line by accident and add a sub-line there
'Only go through conditionals for a reset if there is actual split quantity to continue updating, and not at the end of potential dates to update yet
Do While Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) = 0 And KTCurrDateColumn < KTFinalDateColumn + 1
KTCurrDateColumn = KTCurrDateColumn + 1
Loop
If KTCurrDateColumn = KTFinalDateColumn + 1 Or KTCurrDateColumn > KTFinalDateColumn + 1 Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) = 0 Then
Exit Do
End If
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) <> 0 And KTCurrDateColumn < KTFinalDateColumn + 1 Then
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = 1
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 1
End If
'Create a new sub-line if we've reached the end of existing PO lines to update but have a new split date and quantity to add off the manufacturer's report
ElseIf Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) <> 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 2) <> 0 Then
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
'Reinitialize current row to point to the new row addition which should be added directly below the current currPORow
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
'Update for both a split date update as well as a split quantity
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(1, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn)
'currPORow = currPORow + 1
KTCurrDateColumn = KTCurrDateColumn + 1
'Only continue if there is actual split quantity to continue updating, and not at the end of potential dates to update yet
Do While Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) = 0 And KTCurrDateColumn < KTFinalDateColumn + 1
KTCurrDateColumn = KTCurrDateColumn + 1
Loop
If KTCurrDateColumn = KTFinalDateColumn + 1 Or KTCurrDateColumn > KTFinalDateColumn + 1 Or Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) = 0 Then
Exit Do
End If
End If 'Final if for checking through PO lines with existing splits
Loop 'Final loop for checking through PO lines with existing splits
'If there are still spare PO split lines after this True Match exercise, need to loop through deleting out their balance quantities to close them out
'Existing sub-lines will have a line number, but no item code
'Spare PO-side split lines are eliminated as soon as stepping into the next PO row would result in hitting a non-blank PO-side item code
'The end of the outer loop then properly results in stepping into a new PO line item
'There was a PO match so the current line is guaranteed not to be a spill-over into another PO line or PO sub-line.
'However, if we do reach a line number of zero during this check, that means we reached the very end of the PO.
Do While ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 1) <> 0
'Assign the last-known match as the date so it follows the pattern of descending increasing dates
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 8) = ThisWorkbook.Sheets(1).Cells(currPORow, 8)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11) = currPOLineQty - ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12) = 0
currPORow = currPORow + 1
Loop
'Exit this script if the entire PO ended on a split line and thus needs to exit due to blank lines at the end
'That means both a blank item code as well as blank line number were encountered at once
If ThisWorkbook.Sheets(1).Cells(currPORow + 1, 1) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 Then
Exit Sub
End If
'Sum all of the split instances of a matched multiple line item in the PO (e.g. the primary line and all of its sub-lines, which are characterized by blank item code cells). Sub-lines will be deleted later as necessary in a different check if the item match check fails, because this particular summation process should only occur if a PO number and item number match occurs which will not happen with a blank item code cell. Only enter this if we aren't on a sub-line already.
If ThisWorkbook.Sheets(1).Cells(currSplitLine, 2) <> 0 And ThisWorkbook.Sheets(1).Cells(currSplitLine, 1) <> 0 Then
currSumRow = currSplitLine
currPrimaryLineNumber = ThisWorkbook.Sheets(1).Cells(currSumRow, 1)
'Initialize itemCodeSum to the primary line's # of units, then any associated sub-line units will be added to this in the following segment
itemCodeSum = itemCodeSum + ThisWorkbook.Sheets(1).Cells(currSumRow, 11)
itemBalanceSum = itemBalanceSum + ThisWorkbook.Sheets(1).Cells(currSumRow, 12)
currSumRow = currSumRow + 1
'Add the # of units involved in sub-lines to itemCodeSum. Sub-lines are characterized by rows with blank item code columns, succeeding a row that has data in the item code column. But, it can't be a completely blank row or an infinite summation attempt may happen.
Do Until currPrimaryLineNumber <> ThisWorkbook.Sheets(1).Cells(currSumRow, 1)
itemCodeSum = itemCodeSum + ThisWorkbook.Sheets(1).Cells(currSumRow, 11)
itemBalanceSum = itemBalanceSum + ThisWorkbook.Sheets(1).Cells(currSumRow, 12)
currSumRow = currSumRow + 1
Loop
'Check if this total sum matches the production report for that order number and item number combination
If itemCodeSum <> Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, factoryTotalOnOrderCol) Then 'If totals don't match between PO and report, outline the differences in sheet 10
ThisWorkbook.Sheets(10).Cells(currTotalMismatchReportRow, 1) = ThisWorkbook.Sheets(1).Cells(2, 1)
ThisWorkbook.Sheets(10).Cells(currTotalMismatchReportRow, 2) = ThisWorkbook.Sheets(1).Cells(currPORow, 1)
ThisWorkbook.Sheets(10).Cells(currTotalMismatchReportRow, 3) = ThisWorkbook.Sheets(1).Cells(currPORow, 2)
ThisWorkbook.Sheets(10).Cells(currTotalMismatchReportRow, 4) = ThisWorkbook.Sheets(1).Cells(currPORow, 8)
ThisWorkbook.Sheets(10).Cells(currTotalMismatchReportRow, 5) = itemCodeSum
ThisWorkbook.Sheets(10).Cells(currTotalMismatchReportRow, 6) = itemBalanceSum
ThisWorkbook.Sheets(10).Cells(currTotalMismatchReportRow, 7) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, factoryTotalOnOrderCol)
ThisWorkbook.Sheets(10).Cells(currTotalMismatchReportRow, 8) = ThisWorkbook.Sheets(1).Cells(2, 18)
End If
'workbookMatchRow = workbookMatchRow + 1
currTotalMismatchReportRow = currTotalMismatchReportRow + 1
itemCodeSum = 0
itemBalanceSum = 0
currSumRow = currPORow
End If 'End for summing up and reporting the sub-line quantities elsewhere
End If 'End for the entire situation of split-lines existing on PO
'For single PO lines, check if there are any split lines on the manufacturing report first to decide if any split lines on the PO side need to be added.
'If not then the standard single-line update procedure can be done
'Can dump directly into a single split-line situation from here after checking other situations, so KTCurrDateColumn cannot be re-initialized here
'For the summation check to see if there are any split lines to create, need to use whatever KTCurrDateColumn is set at but use a separate counter for checking if there are any remaining date splits
KTSplitQtySum = 0
'KTCurrDateColumn = 28
'KTFinalDateColumn = 43
KTCurrDateColumnRemaining = KTCurrDateColumn
'Check if there actually is any split quantity in the manufacturing report's remaining far-right columns
Do While KTCurrDateColumnRemaining < KTFinalDateColumn + 1
KTSplitQtySum = KTSplitQtySum + Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumnRemaining)
KTCurrDateColumnRemaining = KTCurrDateColumnRemaining + 1
Loop
'Single PO line situation requiring new split-outs
'The problem here is if a receipt has already been done against a single-line item that is still open,
'then we need to make a new split line immediately and dump the balance immediately onto a new line
If KTSplitQtySum > 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) <> 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 12) <> 0 And ThisWorkbook.Sheets(2).Cells(2, 11) = 0 And KTCurrDateColumn <> KTFinalDateColumn + 1 Then
'KTCurrDateColumn = 28
'Add first split date and quantity to the existing single primary line
Do While Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) = 0
KTCurrDateColumn = KTCurrDateColumn + 1
Loop
'Close out primary line and add an immediate new split line if receipts would go negative to update the quantity according to the vendor report or if the line was closed already,
'and add the first manufacturing report split date to that first new split line
'''''
If ((ThisWorkbook.Sheets(1).Cells(currPORow, 12) - (ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn)) < 0) Or ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 0) Then
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow, 11)
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = ThisWorkbook.Sheets(1).Cells(currPORow, 11) - ThisWorkbook.Sheets(1).Cells(currPORow, 12)
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = 0
'Update date of closed line so it is potentially not out later than later lines
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(1, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
'Update for both a split date update as well as a split quantity
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(1, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = ThisWorkbook.Sheets(1).Cells(currPORow, 11)
'Should short-circuit the next conditional if this was both the final currDateColumn and also had a quantity in it
KTCurrDateColumn = KTCurrDateColumn + 1
'Assign vendor quantity if the balance would not go negative, and if the original balance is greater than 0 to begin with
ElseIf (ThisWorkbook.Sheets(1).Cells(currPORow, 12) - ((ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn))) > 0) And ThisWorkbook.Sheets(1).Cells(currPORow, 12) > 0 Then
'If a placeholder split line of 1 unit and 1 balance were added due to previous code, then the current balance
'cannot be used in this calculation.
'Need to set total quantity and balance quantity to be equal in this case.
'Clear ETD and ETA to let system calculate them. If they are present on split lines, the entire PO cannot be uploaded under all circumstances
ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
If ThisWorkbook.Sheets(1).Cells(currPORow, 12) <> ThisWorkbook.Sheets(1).Cells(currPORow, 11) Then
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(1, KTCurrDateColumn)
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow, 12)
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = currPOLineQty - ((ThisWorkbook.Sheets(1).Cells(currPORow, 11) - Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, quantColumn)))
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn)
'Clear ETD and ETA to let system calculate them. If they are present on split lines, the entire PO cannot be uploaded under all circumstances
Else
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(1, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn)
End If
'Move on to the next split date. If there actually only was 1 date listed in the split columns, the split date conditionals should be short-circuited
'Should short-circuit the next conditional if this was both the final currDateColumn and also had a quantity in it
KTCurrDateColumn = KTCurrDateColumn + 1
End If
'Add split lines (or additional splits) after dealing with the single PO parent line situation
'Issue - if there are still blank KT date columns to check, it is going directly to the Else clause anyway even if the column truly is blank or Null
Do While KTCurrDateColumn < KTFinalDateColumn + 1
'Find the next non-blank quantity in the manufacturer's split-out column and keep looping with no action if that hasn't happened
If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn) = 0 Then
KTCurrDateColumn = KTCurrDateColumn + 1
If KTCurrDateColumn = KTFinalDateColumn + 1 Then
Exit Do
End If
Else:
ThisWorkbook.Sheets(1).Rows(currPORow + 1).Insert
'Reinitialize current row to point to the new row addition which should be added directly below the current currPORow
currPORow = currPORow + 1
ThisWorkbook.Sheets(1).Cells(currPORow, 1) = currPOLineNumber
'Update for both a split date update as well as a split quantity
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(1, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 11) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, KTCurrDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 12) = ThisWorkbook.Sheets(1).Cells(currPORow, 11)
'currPORow = currPORow + 1
KTCurrDateColumn = KTCurrDateColumn + 1
End If
Loop 'Final loop for adding split lines to single PO line situations
'If there are still spare PO split lines after this True Match exercise, need to loop through deleting out their balance quantities to close them out
'Existing sub-lines will have a line number, but no item code
'Spare PO-side split lines are eliminated as soon as stepping into the next PO row would result in hitting a non-blank PO-side item code
'The end of the outer loop then properly results in stepping into a new PO line item
'However, also cannot continue this if we reached the end of the PO as a result (line number cell is 0)
Do While ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 1) <> 0
'Assign the last-known match as the date so it follows the pattern of descending increasing dates
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 8) = ThisWorkbook.Sheets(1).Cells(currPORow, 8)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 10).ClearContents
currPOLineQty = ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 11) = currPOLineQty - ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12)
ThisWorkbook.Sheets(1).Cells(currPORow + 1, 12) = 0
currPORow = currPORow + 1
Loop
End If 'End for adding split lines to single PO line situations
'Single line update situation with no new split-outs to make
'Only make a date update to the single line if the local order data indicates there are receivables left which have not been input (therefore, they have not appeared on an invoice yet or not physically received yet if there is a policy of no virtual receipts until physical receipt
'If Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, revisedDateColumn) <> 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 12) <> 0 Then
If KTSplitQtySum = 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 12) <> 0 And ThisWorkbook.Sheets(2).Cells(2, 11) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow + 1, 2) <> 0 And KTCurrDateColumn <> KTFinalDateColumn + 1 Then 'User-defined parameter is blank in this case. However I've updated for this check to not even matter because we don't want to update ETD and ETA under this version of the program. They both need to be cleared out.
ThisWorkbook.Sheets(1).Cells(currPORow, 8) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, revisedDateColumn)
ThisWorkbook.Sheets(1).Cells(currPORow, 9) = ThisWorkbook.Sheets(1).Cells(currPORow, 8)
If ThisWorkbook.Sheets(2).Cells(2, 11) <> 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 12) <> 0 Then
ThisWorkbook.Sheets(1).Cells(currPORow, 9) = ThisWorkbook.Sheets(1).Cells(currPORow, 8)
ThisWorkbook.Sheets(1).Cells(currPORow, 10) = ThisWorkbook.Sheets(1).Cells(currPORow, 9) + daysToArrival
Else 'let arrival date be calculated by the ERP system in this case. That means no-one indicated a 'days to arrival' padding parameter
ThisWorkbook.Sheets(1).Cells(currPORow, 9) = ThisWorkbook.Sheets(1).Cells(currPORow, 8)
ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
End If
If KTSplitQtySum = 0 And ThisWorkbook.Sheets(1).Cells(2, 13) <> 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 12) <> 0 And KTCurrDateColumn <> KTFinalDateColumn + 1 Then
ThisWorkbook.Sheets(6).Cells(currChangedCustNumberRow, 1) = ThisWorkbook.Sheets(1).Cells(2, 13)
ThisWorkbook.Sheets(6).Cells(currChangedCustNumberRow, 2) = ThisWorkbook.Sheets(1).Cells(currPORow, 1)
currChangedCustNumberRow = currChangedCustNumberRow + 1
End If
Else
If KTSplitQtySum = 0 And ThisWorkbook.Sheets(2).Cells(2, 11) <> 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 12) <> 0 And KTCurrDateColumn <> KTFinalDateColumn + 1 Then 'use user-defined arrival is used instead if that parameter is used, rather than the primary 'if' statement. Otherwise the same basic condition has to be true in order for this to work.
'ThisWorkbook.Sheets(1).Cells(currPORow, 9) = ThisWorkbook.Sheets(1).Cells(currPORow, 8)
ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
ThisWorkbook.Sheets(1).Cells(currPORow, 10) = ThisWorkbook.Sheets(1).Cells(currPORow, 9) + daysToArrival
'Else 'let arrival date be calculated by the ERP system
' 'ThisWorkbook.Sheets(1).Cells(currPORow, 9) = ThisWorkbook.Sheets(1).Cells(currPORow, 8)
' ThisWorkbook.Sheets(1).Cells(currPORow, 9).ClearContents
' ThisWorkbook.Sheets(1).Cells(currPORow, 10).ClearContents
End If
If KTSplitQtySum = 0 And ThisWorkbook.Sheets(1).Cells(2, 13) <> 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 12) And KTCurrDateColumn <> KTFinalDateColumn + 1 <> 0 Then
ThisWorkbook.Sheets(6).Cells(currChangedCustNumberRow, 1) = ThisWorkbook.Sheets(1).Cells(2, 13)
ThisWorkbook.Sheets(6).Cells(currChangedCustNumberRow, 2) = ThisWorkbook.Sheets(1).Cells(currPORow, 1)
currChangedCustNumberRow = currChangedCustNumberRow + 1
End If
Exit Do
'Force exit if we've found a match and finished processing it, or else it will still keep running against the exact same True match without moving on to the next PO line in the below outer loop code
'If it does keep cycling to the final workbookMatchRow via the below Else clause, then the Do should naturally terminate without ever having found a True match
End If 'End for updating single lines with no split outs
'Goal for an item code + PO match but with zero qty open on that row would be to go through all instances of such matches in total qty and reconcile it with balance qty on the PO side
'ElseIf ThisWorkbook.Sheets(1).Cells(currPORow, 2) = Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, itemMatchColumn) And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, POCheckColumn) = PONumber And Workbooks(fileNameMatch).Sheets(fileNameMatchSheetIndex).Cells(workbookMatchRow, openQtyColumn) = 0 Then
End If 'End for updating and splitting out (if necessary) an open quantity row on the vendor status report
'Standard re-initialization procedure for the inner loop if blank item code rows did not have to be deleted, and the loop continues assuming that there are still rows to check in the other workbook. Otherwise, this loop will terminate.
'It is ending on a blank PO item code sometimes now but still hasn't gotten to the inner loop condition of a blank workbookMatchRow cell
'This is happening if a match wasn't found so it moved on to a blank item code row of the non-matching entity
Else
'Get through sub-lines of item codes that didn't match in the KT workbook, especially if closed duplicate rows were deleted from the manufacturer's Status Report
Do While ThisWorkbook.Sheets(1).Cells(currPORow, 2) = 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 1) <> 0
currPORow = currPORow + 1
Loop
If ThisWorkbook.Sheets(1).Cells(currPORow, 2) <> 0 Then
workbookMatchRow = workbookMatchRow + 1
End If
End If 'End for whether there was a True match ever found at all
Loop 'Inner loop end
'What to do if a match was not found at all with the production report versus the system order number and item number combination, from the 'main loop'. However this doesn't include sub-lines (rows with blank item codes), which are dealt with differently. They just fill up space in the error log if they are captured here.
If ItemMatch = False And ThisWorkbook.Sheets(1).Cells(currPORow, 12) <> 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 2) <> 0 Then 'Report items not on the report at all
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 1) = ThisWorkbook.Sheets(1).Cells(2, 1)
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 2) = ThisWorkbook.Sheets(1).Cells(currPORow, 1)
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 3) = ThisWorkbook.Sheets(1).Cells(currPORow, 2)
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 4) = ThisWorkbook.Sheets(1).Cells(currPORow, 4)
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 5) = ThisWorkbook.Sheets(1).Cells(2, 18)
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 6) = ThisWorkbook.Sheets(1).Cells(currPORow, 8)
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 7) = ThisWorkbook.Sheets(1).Cells(currPORow, 11)
ThisWorkbook.Sheets(8).Cells(currMissingItemCodeLogRow, 8) = ThisWorkbook.Sheets(1).Cells(currPORow, 12)
currMissingItemCodeLogRow = currMissingItemCodeLogRow + 1
End If
'Document the date if it is below a cut-off
currDateCheck = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 8)
If (currDateCheck < pastDueDate Or currDateCheck = pastDueDate) And ThisWorkbook.Sheets(1).Cells(currPORow, 12) <> 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 1) <> 0 And ThisWorkbook.Sheets(1).Cells(currPORow, 2) <> 0 Then 'Only make a documentation if there are still receivables left, it is a primary line and is below the cut-off date
ThisWorkbook.Sheets(7).Cells(currPORow, 1) = ThisWorkbook.Sheets(1).Cells(2, 1)
ThisWorkbook.Sheets(7).Cells(currPORow, 2) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 1)
ThisWorkbook.Sheets(7).Cells(currPORow, 3) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 2)
ThisWorkbook.Sheets(7).Cells(currPORow, 4) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 4)
ThisWorkbook.Sheets(7).Cells(currPORow, 5) = ThisWorkbook.Sheets(1).Cells(2, 18)
ThisWorkbook.Sheets(7).Cells(currPORow, 6) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 8)
ThisWorkbook.Sheets(7).Cells(currPORow, 7) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 11)
ThisWorkbook.Sheets(7).Cells(currPORow, 8) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 12)
'Increment for the next pass through the inner loop, because we have logged a new result
pastDueCheckRow = pastDueCheckRow + 1
End If
'Standard re-initialization for loop iteration
currPORow = currPORow + 1
workbookMatchRow = ThisWorkbook.Sheets(2).Cells(2, 7)
KTSplitQtySum = 0
KTCurrDateColumn = 28
KTCurrDateColumnRemaining = 28
'Re-initialize the match variables to 'false' for the next iteration
ItemMatch = False
ItemMatch2 = False
matchComplete = False
alreadyFoundMatch = False
alreadyFoundMatch2 = False
Loop 'Outer loop end
'Post-loop check
''''''' Check if any dates have not truly been updated, e.g. report open units left on items that don't meet the exit-factory cut-off date specified
' currDateCheck = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 8)
'
' Do While ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 1) <> 0
'
' If (currDateCheck < pastDueDate Or currDateCheck = pastDueDate) And ThisWorkbook.Sheets(1).Cells(currPORow, 12) <> 0 Then
' ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 1) = ThisWorkbook.Sheets(1).Cells(2, 1)
' ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 2) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 1)
' ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 3) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 2)
' ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 4) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 4)
' ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 5) = ThisWorkbook.Sheets(1).Cells(2, 18)
' ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 6) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 8)
' ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 7) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 11)
' ThisWorkbook.Sheets(7).Cells(pastDueCurrReportRow, 8) = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 12)
'
' pastDueCheckRow = pastDueCheckRow + 1
' pastDueCurrReportRow = pastDueCurrReportRow + 1
'
' currDateCheck = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 8)
' Else
'
' pastDueCheckRow = pastDueCheckRow + 1
'
' currDateCheck = ThisWorkbook.Sheets(1).Cells(pastDueCheckRow, 8)
' End If
'
' Loop
End Sub