Hi. I have attached a simplified version of a stock system we run currently. Basically we grow fruit plants and each time for example a batch of grape plants are planted we assign a batch no to the product and predict the losses we will have (usually 95%) THis then gives us a column titled saleable Qty and column titled predicted loss.

On another sheet I want to import invoices frm our invoice system. I know how to do that in vba what I am not sure on how to do is when I hit a command button on the worksheet I want the invoices to deplete the stock. Here is the problem, say for example I have the following data
Please Login or Register  to view this content.
Scenarios 1 - the invoice contains 1 order for 100 oranges so I would imagine I would run a loop and deplete the first batch no in the list. However if the order was for 600 oranges none of the batches have enough stock so I would need it to loop into batch 1 remove 475 units form stock (and add 475 to sold figure) then loop to batch 2 and remove 125 units from that batch (and add 125 to sold figure).

Scenario 2 - Taking it a step further, if the order was for 1480 oranges I would need it to loop through all three batches and deplete saleable to zero in each and add 475 to each sold column. There would still be 55 units outstanding. I would then want it to look in predicted loss and see there was the potential for another 25 units in batch one so add that figure to the sold column, do the same for batch 2 and then on batch 3 see there was a potential for 25 sales but only add 5 more to sold column as that is all that is needed to complete the order.

Apologies if I have not explained this very well. If clarification is required, please ask.

Any help will be much appreciated. THe batch list can run up to 2000 batches - not sure if this will influence whether you do a loop or advanced filter first or make no impact at all.