零售企业跨门店滞销库存分配VBA代码故障排查及解决方案咨询

Let's tackle your three questions one by one, and fix the problems in your current code to meet your retail inventory allocation needs.

1. What's Wrong With Your Do Until Loop? #

Your loop has three critical issues that prevent it from working as expected:

• No reset of z per product: You declare z outside the For x loop, so when you move to the next product (next row), z retains the value from the previous product. If the first product hits z=318, all subsequent products will skip the Do Until loop entirely.
• Hardcoded allocation limit: Using a fixed value 318 means every product uses the same allocation cap, but your requirement calls for a product-specific limit (the total stagnant inventory for that item, since you stop when allocatable stock is exhausted).
• Full round of allocations before checking the limit: Your current code allocates to all 10 stores in one loop iteration before checking if z has reached the limit. This means you'll over-allocate if the total of one full round exceeds the remaining allocatable stock, and you can't stop mid-round when the cap is hit.

2. Can You Check the Termination Condition Inside the Loop? #

Absolutely! This is actually the right approach to stop immediately when your allocation cap is reached. You can use a Do...Loop with an Exit Do statement inside the loop body. After allocating to each individual store, calculate the current total z and check if it's reached or exceeded the product's allocatable limit. If yes, exit the loop right away.

Here's how this would look in practice (integrated into your code):

Sub Stock_Transfers()
    Dim x As Integer
    Dim z As Double
    Dim storeCol As Integer
    Dim allocatableLimit As Double ' Product-specific allocation cap
    
    ' Loop through each product row
    For x = Range("A4").Row To Range("A4").End(xlDown).Row
        z = 0 ' Reset total allocated for each new product
        ' Replace [STAGNANT_TOTAL_COL] with your actual column number for total stagnant stock
        allocatableLimit = Cells(x, [STAGNANT_TOTAL_COL]).Value
        
        Do
            ' Loop through each of the 10 stores (columns 74 to 83)
            For storeCol = 74 To 83
                ' Calculate the amount to allocate for this store
                Dim allocateAmount As Double
                allocateAmount = Range("BZ2").Value * Cells(x, storeCol).Offset(0, -45).Value
                
                ' Check if adding this amount would exceed the limit
                If z + allocateAmount > allocatableLimit Then
                    ' Only allocate the remaining stock
                    allocateAmount = allocatableLimit - z
                    Cells(x, storeCol).Value = Cells(x, storeCol).Value + allocateAmount
                    z = allocatableLimit ' Set z to limit to trigger exit
                    Exit For ' Stop allocating to stores for this round
                Else
                    ' Allocate full amount
                    Cells(x, storeCol).Value = Cells(x, storeCol).Value + allocateAmount
                    z = z + allocateAmount
                End If
                
                ' Exit early if we've hit the limit
                If z >= allocatableLimit Then Exit For
            Next storeCol
            
            ' Exit the Do loop once we've exhausted allocatable stock
            If z >= allocatableLimit Then Exit Do
        Loop
    Next x
End Sub

Key improvements here:

• z is reset to 0 for every product
• Allocation limit is product-specific
• We check after each store allocation if we've hit the cap, and stop immediately
• We handle partial allocations if a full store allocation would exceed the remaining stock

3. Alternative Solutions: Using Solver for Inventory Allocation #

If you want a more optimized allocation (e.g., prioritizing stores based on sales demand instead of a round-robin), Excel's Solver tool is a great fit. First, make sure the Solver add-in is enabled (File > Options > Add-ins > Manage Excel Add-ins > Check "Solver Add-in").

Solver VBA Code Example #

Sub SolverStockAllocation()
    Dim x As Integer
    Dim variableRange As Range
    Dim allocatableLimit As Double
    
    ' Loop through each product row
    For x = Range("A4").Row To Range("A4").End(xlDown).Row
        ' Define ranges for this product
        Set variableRange = Range(Cells(x, 74), Cells(x, 83)) ' Allocation amounts per store
        ' Replace [STAGNANT_TOTAL_COL] with your actual column number for total stagnant stock
        allocatableLimit = Cells(x, [STAGNANT_TOTAL_COL]).Value
        
        ' Set up Solver to maximize total allocated stock (adjust goal as needed)
        SolverOk SetCell:=variableRange, _
            MaxMinVal:=1, ' 1 = Maximize, 2 = Minimize, 3 = Set to specific value
            ValueOf:=0, _
            ByChange:=variableRange, _
            Engine:=1, ' GRG Nonlinear engine (use 2 for Simplex LP if allocations need to be integers)
            EngineDesc:="GRG Nonlinear"
        
        ' Add constraint: Total allocation <= allocatable limit
        SolverAdd CellRef:=variableRange, _
            Relation:=1, _
            FormulaText:=allocatableLimit
        
        ' Add constraint: Allocations can't be negative
        SolverAdd CellRef:=variableRange, _
            Relation:=3, _
            FormulaText:=0
        
        ' Optional: Add constraint to match store demand proportions
        ' SolverAdd CellRef:=variableRange, _
        '     Relation:=2, _
        '     FormulaText:="=" & Range("BZ2").Value & "*" & Cells(x, storeCol).Offset(0, -45).Address
        
        ' Run Solver without showing the results dialog
        SolverSolve UserFinish:=True
        
        ' Clear Solver settings for the next product
        SolverReset
    Next x
End Sub

Solver Parameter Breakdown: #

• SolverOk:
  • SetCell: The range you want to optimize (here, we maximize total allocated stock)
  • MaxMinVal: Defines your optimization goal (maximize, minimize, or set to a value)
  • ByChange: The cells Solver can modify (your 10 store allocation columns)
  • Engine: Choose based on your needs—1 for proportional allocations, 2 for integer stock amounts
• SolverAdd:
  • Relation: 1 = <=, 2 = =, 3 = >=
  • FormulaText: The constraint value (e.g., total allocatable stock, 0 for non-negative allocations)

内容的提问来源于stack exchange，提问作者kshah