Help with Generating Correct Replenishment Figures based on available stock

[Chriz]

Hi All,

I'm working with a new retailer at work, and wondered if anyone could give me some help on creating the replenishment figures based on our available stock figures.

I've already got a fixed replenishment of 4 units based on the retailers in stock figures that will be provided by spreadsheet for a number of their stores, and I've mostly got the logic down for that, but what I'd also like to incorporate into the replenishment orders for them is to take into consideration our available stock, so that I wouldn't try and order 4 copies on a replen (or whatever the replen amount is), if we don't have the stock.

Currently I apply the below macro to generate the figures based on their figures:

Please Login or Register  to view this content.

But what I'd like to do now, is look at an additional sheet, that has the current IN STOCK figure, and using those figures to make sure that I don't send a replen for a qty that exceeds our available stock. Now I know I could add helper columns that shows the starting stock figure and then add some logic if I loop down the row, and then across the columns, but I was wondering if there was a more elegant solution by storing the available stock figure as a variable somehow, and then the macro would deduct the replen off of the available stock, until it hits zero, and then just display the replen as zero, without the need for either helper columns or formulas. I've attached a sample, if anyone can take a look. I am open to amending the macro to use For loops to loop down and then across rather than For Each. This is actually going to be a lot more than the sample data but the date 'should' stay similar to the attached, there will just be more rows, and more columns.

Thanks in advance!

Chriz

[Chriz]

Hi All,

I put my thinking cap on - and came up with a solution by myself after some time going back through my notes on variant arrays etc. Here is the macro I used to loop across the columns, then down the rows, factoring free stock from another sheet called 'In Stock' may be useful to someone else looking to do something similar:

Please Login or Register  to view this content.

Will mark this as solved.

Thansk

Chriz