+ Reply to Thread
Results 1 to 8 of 8

Macro to iteratively allocate stock each day

  1. #1
    Registered User
    Join Date
    07-22-2017
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    7

    Lightbulb Macro to iteratively allocate stock each day

    Hi all,

    I'm looking for help with a stock forecasting model via formulae or macro

    The model currently requires manual allocation of either 'B' or 'ZA' stock each day based on net demand (called 'Net Number' for each type in the spreadsheet), prioritising larger Net Number then 'B' type if Net Numbers are equal. After allocation, the Stock Level and Net Number will reduce based on Usage and then the same occurs for the following day. Stock levels will also increase when Deliveries land (columns G and H)

    'Usage' depends on a few rules:
    • Maximum daily usage - 'B' allocation takes two days to complete so daily usage can be either 0.5 'B' type or '1 'ZA' type per day. If allocating B, next allocation must be 0.5 'B' type (i.e. to finish the previous day's allocation)
    • Stock Level - only use when stock level >=1 (unless 2nd day of 'B' type, where stock can be >=0.5) and cannot be less than 0
    • Usage prioritises larger Net Number value, then 'B' type
    • Avoiding weekends and public holidays (rows highlighted in red)

    I've also attached a screenshot of a couple of working examples

    Hopefully that explains what I'm after, happy to provide more info if anything's unclear.

    Appreciate all and any help, so thanks in advance!
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Macro to iteratively allocate stock each day

    I assume days are WORKING days so current day is Friday so next day is Monday for "B" stock.

    In your file there appear to be inconsistencies in holiday: 02/01/2022 is flagged as holiday (column B) but not highlighted in red and converse for 04/01/2022. Holidays also are limited to Christmas/New Year ???
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Registered User
    Join Date
    07-22-2017
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    7

    Re: Macro to iteratively allocate stock each day

    Quote Originally Posted by JohnTopley View Post
    I assume days are WORKING days so current day is Friday so next day is Monday for "B" stock.

    In your file there appear to be inconsistencies in holiday: 02/01/2022 is flagged as holiday (column B) but not highlighted in red and converse for 04/01/2022. Holidays also are limited to Christmas/New Year ???
    Hi John, thanks for taking the time

    Yes that's correct - working days only

    Ah yes, good spot - Column B was a last minute addition and should supersede the red highlight (this obviously also affects the current manually inserted Usage)

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Macro to iteratively allocate stock each day

    Please Login or Register  to view this content.
    See sheet "Forecast_Results"

    For testing I copied "Forecast" to "Forecast_Results": the VBA updates the Usage columns in "Forecast_Results".

    The VBA can be changed to update the original "Forecast" as required.

    NOTE: The last 2 conditions listed are duplicates so I have assumed this is a typo: If there should be a another condition then the VBA will need to be updated
    Attached Files Attached Files
    Last edited by JohnTopley; 12-30-2021 at 09:06 AM.

  5. #5
    Registered User
    Join Date
    07-22-2017
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    7

    Re: Macro to iteratively allocate stock each day

    Hi John - thanks for that, really appreciate it

    I've tried running it however the only output seems to be copying the columns from "Forecast" to the "Forecast_Results" sheet, the usage columns don't seem to be populating in either worksheets

    Also, is my understanding correct that the formulae in "Forecast" 'Stock Level' and 'Net Number' columns are still utilised to calculate the next day's values or is this re-calculated within the VBA?

    I'd prefer to keep it within the one sheet, if not too much to ask could you please update the VBA accordingly or advise how to do it?

    RE your note, the last condition should have been "IF 'B' Net Number is < 'ZA' Net Number and 'ZA' Stock Level <1, do nothing"

    Thanks in advance

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Macro to iteratively allocate stock each day

    Please Login or Register  to view this content.
    Updated
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-22-2017
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    7

    Re: Macro to iteratively allocate stock each day

    Hi John, I tried to test but I can't get the Macro update the columns. e.g. if I change values in the 'Count' columns or delete values in 'Usage' or 'Net Number' the spreadsheet doesn't change

    I also noticed that some cells in 'Usage' are blank but the 'Net Number' has reduced, is there a reason for this?

    Cheers
    Attached Images Attached Images

  8. #8
    Registered User
    Join Date
    07-22-2017
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    7

    Re: Macro to iteratively allocate stock each day

    Hi John, just following up on this

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA / Macro to copy and paste 100 rows iteratively
    By Ted Dennis in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-16-2021, 06:10 AM
  2. How to automatically allocate stock to pallet with a limit space
    By Vi Nguyenn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-04-2021, 09:15 AM
  3. Replies: 1
    Last Post: 04-26-2020, 09:29 AM
  4. Replies: 1
    Last Post: 05-23-2016, 10:23 PM
  5. Replies: 31
    Last Post: 10-28-2015, 01:54 AM
  6. Replies: 1
    Last Post: 02-17-2015, 12:12 AM
  7. Macro for iteratively updating a grid based on inputs
    By swagatkarnany in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-21-2012, 02:18 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1