问防止商品库存量低于零的机制

EN

Sub PlaceOrder()
    With ActiveSheet
        Dim tbl As ListObject
        Set tbl = .ListObjects("Table1")
        
        Dim LastRow As Long
        LastRow = tbl.Range.Rows.Count    'get # of last row

        '====== Copy all content `
        'copy and paste A4
        .Range("A4").Copy
        tbl.Range(LastRow, 1).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

        'copy and paste B4
        .Range("B4").Copy
        tbl.Range(LastRow, 2).Offset(1).PasteSpecial Paste:=xlPasteFormulas

        'copy and paste C4:F4
        .Range("C4:F4").Copy
        tbl.Range(LastRow, 3).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

        'clear value in B4:E4
        .Range("B4:E4").ClearContents
    End With
    
    Dim stockList As Variant
    Dim qtyList As Variant
    stockList = tbl.ListColumns("Stock (Exchange:Ticker)").DataBodyRange.Value
    qtyList = tbl.ListColumns("Qty").DataBodyRange.Value
    
    '=== Start checking inventory of all stocks ===
    Dim inventoryDict As Object
    Set inventoryDict = CreateObject("Scripting.Dictionary")
    Dim i As Long
    
    'Get a unique list of stocks
    For i = 1 To UBound(stockList, 1)
        If inventoryDict.Exists(stockList(i, 1)) Then
            inventoryDict(stockList(i, 1)) = inventoryDict(stockList(i, 1)) + qtyList(i, 1)
        Else
            inventoryDict.Add stockList(i, 1), qtyList(i, 1)
        End If
    Next i
    
    'Loop through each stock and check if it's < 0
    Dim dictItem As Variant
    For Each dictItem In inventoryDict
        If inventoryDict(dictItem) < 0 Then
            'Inventory is negative
            MsgBox "Display Message that inventory is not in stock" & vbNewLine & vbNewLine & _
                    dictItem & ": " & inventoryDict(dictItem)
            tbl.ListRows(tbl.ListRows.Count).Delete
            Exit For
        End If
    Next dictItem
End Sub