Thiết kế website giá rẻ

Question

I am trying to create a vba code that gives me the numbers I have to put in the demand plan adjustments table to reflect a ratio of 2.5 and 3 in the budget volumes & pts consumption ratio. Find below my data and the code that I tried

Total in-market pt consumption (calculation)

Month: Jan-24, Feb-24, Mar-24, Apr-24, May-24, Jun-24, Jul-24, Aug-24, Sep-24, Oct-24, Nov-24, Dec-24, Jan-25, Feb-25, Mar-25, Apr-25, May-25, Jun-25, Jul-25, Aug-25, Sep-25, Oct-25, Nov-25, Dec-25, Jan-26, Feb-26, Mar-26, Apr-26, May-26, Jun-26, Jul-26, Aug-26, Sep-26, Oct-26, Nov-26, Dec-26

Product A: 
1842, 512, 565, 473, 482, 548, 29, 32, 23, 18, 88, 29, 84, 21, 15, 34, 9, 91, 82, 50, 81, 76, 2, 17, 13, 18, 39, 7, 85, 12, 0, 64, 78, 79, 37, 87, 42, 55, 19

Product B: 
2209, 652, 509, 585, 714, 461, 55, 23, 64, 40, 22, 79, 29, 76, 61, 96, 26, 14, 71, 92, 13, 9, 86, 33, 93, 64, 55, 34, 80, 84, 21, 61, 10, 62, 17, 15, 9, 71, 45

Product C: 
1962, 518, 630, 566, 465, 581, 60, 26, 88, 21, 61, 65, 6, 22, 1, 80, 93, 43, 32, 51, 51, 42, 22, 36, 11, 26, 31, 62, 94, 7, 50, 84, 61, 52, 96, 23, 32, 67, 2

Product D: 
2537, 636, 776, 641, 569, 871, 63, 61, 35, 12, 72, 39, 76, 22, 76, 35, 75, 75, 85, 65, 40, 5, 36, 45, 98, 4, 73, 35, 31, 52, 57, 87, 90, 95, 85, 73, 57, 37, 77

Company (ExF actuals-DP)
Month: Jan-24, Feb-24, Mar-24, Apr-24, May-24, Jun-24, Jul-24, Aug-24, Sep-24, Oct-24, Nov-24, Dec-24, Jan-25, Feb-25, Mar-25, Apr-25, May-25, Jun-25, Jul-25, Aug-25, Sep-25, Oct-25, Nov-25, Dec-25, Jan-26, Feb-26, Mar-26, Apr-26, May-26, Jun-26, Jul-26, Aug-26, Sep-26, Oct-26, Nov-26, Dec-26

Product A: 
11848, 3713, 3454, 2885, 3245, 3732, 118, 28, 368, 510, 74, 255, 78, 353, 202, 460, 12, 427, 63, 147, 587, 334, 165, 384, 593, 401, 140, 253, 178, 0, 68, 326, 535, 284, 477, 189, 218, 487, 439

Product B: 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0

Product C: 
16030, 4339, 3409, 4574, 3979, 4006, 44, 561, 519, 281, 470, 572, 372, 227, 425, 212, 496, 395, 514, 469, 532, 256, 523, 439, 131, 2, 370, 83, 353, 307, 485, 87, 422, 549, 277, 395, 304, 21, 126

Product D: 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0

Demand Plan Adjusments (numbers that I want to get automatically, in order to not do it everytime I have new numbers for the demand plan)

Month: Jan-24, Feb-24, Mar-24, Apr-24, May-24, Jun-24, Jul-24, Aug-24, Sep-24, Oct-24, Nov-24, Dec-24, Jan-25, Feb-25, Mar-25, Apr-25, May-25, Jun-25, Jul-25, Aug-25, Sep-25, Oct-25, Nov-25, Dec-25, Jan-26, Feb-26, Mar-26, Apr-26, May-26, Jun-26, Jul-26, Aug-26, Sep-26, Oct-26, Nov-26, Dec-26

Product A:
Product B:
Product C:
Product D:

Ex-Distributor (Distrib monthly sales report/Demand plan)
Month: Jan-24, Feb-24, Mar-24, Apr-24, May-24, Jun-24, Jul-24, Aug-24, Sep-24, Oct-24, Nov-24, Dec-24, Jan-25, Feb-25, Mar-25, Apr-25, May-25, Jun-25, Jul-25, Aug-25, Sep-25, Oct-25, Nov-25, Dec-25, Jan-26, Feb-26, Mar-26, Apr-26, May-26, Jun-26, Jul-26, Aug-26, Sep-26, Oct-26, Nov-26, Dec-26

Product A: 
3831, 2662, 3354, 462, 154, 427, 248, 372, 436, 298, 469, 361, 118, 374, 112, 53, 41, 461, 261, 592, 217, 211, 251, 249, 14, 200, 112, 143, 240, 282, 599, 138, 285, 323, 553, 142, 340, 296, 13

Product B: 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0

Product C: 
3577, 4286, 3520, 333, 40, 406, 526, 402, 235, 202, 151, 121, 412, 446, 303, 269, 264, 415, 552, 282, 488, 451, 570, 566, 166, 159, 104, 222, 512, 199, 193, 70, 215, 76, 544, 417, 398, 237, 437

Product D: 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0

Stock
Month: Jan-24, Feb-24, Mar-24, Apr-24, May-24, Jun-24, Jul-24, Aug-24, Sep-24, Oct-24, Nov-24, Dec-24, Jan-25, Feb-25, Mar-25, Apr-25, May-25, Jun-25, Jul-25, Aug-25, Sep-25, Oct-25, Nov-25, Dec-25, Jan-26, Feb-26, Mar-26, Apr-26, May-26, Jun-26, Jul-26, Aug-26, Sep-26, Oct-26, Nov-26, Dec-26

Product A: 
849, 539, 604, 71, 82, 21, 91, 15, 96, 90, 91, 50, 80, 82, 80, 70, 24, 71, 56, 35, 43, 75, 68, 1, 78, 15, 3, 40, 91, 82, 39, 89, 8, 71, 32, 54, 2, 19, 77

Product B: 
469, 469, 738, 22, 9, 41, 53, 38, 17, 18, 47, 64, 35, 94, 31, 23, 6, 22, 43, 13, 38, 67, 70, 37, 62, 76, 12, 98, 80, 9, 80, 40, 87, 44, 36, 88, 62, 28, 86

Product C: 
704, 637, 498, 87, 70, 99, 98, 53, 33, 14, 84, 64, 66, 13, 23, 99, 14, 87, 97, 40, 13, 53, 4, 76, 45, 19, 90, 77, 17, 70, 8, 9, 38, 34, 94, 48, 63, 33, 7

Product D: 
589, 554, 300, 20, 36, 39, 76, 88, 55, 24, 27, 30, 89, 19, 86, 42, 48, 20, 52, 55, 64, 34, 53, 49, 87, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25

The tricky thing is that some numbers come from formulas such as the Budget Volumes and Budget Volumes & Pts Consumption Ratio

Budget Volumes = Company (ExF actuals-DP) + Demand Plan Adjustments
Budget Volumes & Pts Consumption Ratio Jan-24 column values = Budget Volumes/Pt Consumption
Budget Volumes & Pts Consumption Ratio from Feb-24 = (stock previous month+budget volumes current month)/pts consumption

I have tried this code but it only works for some cells but not all the cells for the adjusted demand plan

Function CalculateDemandPlanAdjustments()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    
    Dim i As Integer, j As Integer
    Dim targetRatio As Double
    Dim currentRatio As Double
    Dim adjustment As Double
    Dim previousStock As Double
    Dim currentBudgetVolume As Double
    Dim currentPtConsumption As Double
    Dim exfActuals As Double
    Dim iterations As Integer
    Dim maxIterations As Integer
    Dim tolerance As Double
    
    maxIterations = 1000  ' Increased maximum number of iterations
    tolerance = 0.001     ' Tightened tolerance for more precise results
    
    ' Loop through products (rows 24-27)
    For i = 24 To 27
        ' Loop through months (columns H to AQ)
        For j = 8 To 43
            ' Set target ratio (2.5 for odd months, 3 for even months)
            targetRatio = IIf(j Mod 2 = 0, 3, 2.5)
            
            ' Get current values
            previousStock = ws.Cells(i + 21, j - 1).Value ' Stock from the previous month (G45:AP48)
            exfActuals = ws.Cells(i + 16, j).Value ' Companx (ExF actuals-DP)
            currentPtConsumption = ws.Cells(i - 23, j).Value ' Total in-market pt consumption
            
            adjustment = 0
            iterations = 0
            
            ' Iterative adjustment
            If currentPtConsumption > 0 Then
                Do
                    currentBudgetVolume = exfActuals + adjustment
                    currentRatio = (previousStock + currentBudgetVolume) / currentPtConsumption
                    
                    If Abs(currentRatio - targetRatio) <= tolerance Then
                        Exit Do  ' Target ratio achieved
                    End If
                    
                    ' Adjust the adjustment
                    adjustment = adjustment + (targetRatio * currentPtConsumption - previousStock - currentBudgetVolume) / 2
                    
                    iterations = iterations + 1
                Loop Until iterations >= maxIterations
            Else
                currentRatio = 0
            End If
            
            ' Set the calculated adjustment in the Demand Plan Adjustments table
            ws.Cells(i, j).Value = adjustment
            
            ' Update the Budget Volumes cell
            ws.Cells(i + 7, j).Value = currentBudgetVolume
            
            ' Debug output
            Debug.Print "Product " & i & ", Month " & j & ": Ratio = " & currentRatio & ", Target = " & targetRatio & ", Adjustment = " & adjustment
        Next j
    Next i
    
    MsgBox "Demand Plan Adjustments have been calculated and filled in cells H24:AQ27. Budget Volumes have been updated in H31:AQ34."
End Function

Hope someone can give me a hand

Thanks in advance,
Silvia

Thiết kế website giá rẻ

Danh mục

VBA code to automate adjusting numbers based on current static data