Excel VBA Formula Autofill

  • Please assist. I'm new to VBA. I have the code below which calculates stock cover for current month under Column N (Dec 18). Problem is the cells don't adjust as it loops down the file and hence it keeps using Cells M6, K2:M2. I want it to adjust to read the actual sales average for the immediate 3 months and divide with closing stock forecast.


    In words, I want the formula to calculate: (Closing Stock Forecast/average(Gross Inland Sales past 3 Months))*30 per sku



    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 91"]SKU[/TD]
    [TD="width: 154"]Function[/TD]
    [TD="width: 72"]Sep 2018[/TD]
    [TD="width: 72"]Oct 2018[/TD]
    [TD="width: 75"]Nov 2018[/TD]
    [TD="width: 72"]Dec 2018[/TD]
    [TD="width: 72"]Jan 2019[/TD]

    [/tr]


    [tr]


    [td]

    749037

    [/td]


    [td]

    Gross Inland Sales

    [/td]


    [TD="align: right"]86[/TD]
    [TD="align: right"]101[/TD]
    [TD="align: right"]89[/TD]
    [TD="align: right"]65[/TD]
    [TD="align: right"]70[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    Actuals

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Open Orders

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Required

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Closing Stock Forecast

    [/td]


    [TD="align: right"]701[/TD]
    [TD="align: right"]600[/TD]
    [TD="align: right"]511[/TD]
    [TD="align: right"]446[/TD]
    [TD="align: right"]376[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    Days Stock Cover

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]167[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Minimum Stock Level

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]280[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    Minimum Order Quantity

    [/td]


    [TD="align: right"]720[/TD]
    [TD="align: right"]720[/TD]
    [TD="align: right"]720[/TD]
    [TD="align: right"]720[/TD]
    [TD="align: right"]720[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    Lead Time (Days)

    [/td]


    [TD="align: right"]105[/TD]
    [TD="align: right"]105[/TD]
    [TD="align: right"]105[/TD]
    [TD="align: right"]105[/TD]
    [TD="align: right"]105[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    Place Order here

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    742588

    [/td]


    [td]

    Gross Inland Sales

    [/td]


    [TD="align: right"]415[/TD]
    [TD="align: right"]372[/TD]
    [TD="align: right"]312[/TD]
    [TD="align: right"]430[/TD]
    [TD="align: right"]450[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    Actuals

    [/td]


    [td][/td]


    [TD="align: right"]2 160[/TD]
    [TD="align: right"]2 160[/TD]

    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Required

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Closing Stock Forecast

    [/td]


    [TD="align: right"]408[/TD]
    [TD="align: right"]2 196[/TD]
    [TD="align: right"]4 044[/TD]
    [TD="align: right"]3 614[/TD]
    [TD="align: right"]3 164[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    Days Stock Cover

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]167[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Minimum Stock Level

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]1 800[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    Minimum Order Quantity

    [/td]


    [TD="align: right"]2 160[/TD]
    [TD="align: right"]2 160[/TD]
    [TD="align: right"]2 160[/TD]
    [TD="align: right"]2 160[/TD]
    [TD="align: right"]2 160[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    Lead Time (Days)

    [/td]


    [TD="align: right"]105[/TD]
    [TD="align: right"]105[/TD]
    [TD="align: right"]105[/TD]
    [TD="align: right"]105[/TD]
    [TD="align: right"]105[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    Place Order here

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!