Optimise using VBA (without solver)

Originally Posted by Richard Buttrey

What's the aim? Presumably to maximise profit.
Then are you trying to maximise the profit each week or for all five weeks.

In any case isn't the best possible 'mix' simply 6 of each if that's your maximum production capacity. I'm certainly not clear how you are defining permutation 'outcomes'.

Perhaps you'd better upload the workbook and manually add the results you expect for two or three different scenarios

As there are many factors such as delivery time and storage costs the maximum product mix isn’t the best each week as further on in the weeks I go, the less product mix would be more efficient for profitability.

Screen Shot 2018-12-15 at 19.37.16.png

How's this? As you can see many factors affect profit. Including: demand, the cost of not meeting demand (and thus them going to another supplier) etc. The whole point of the VBA code is to not include these factors though - as I want it to be dynamic. The only cells I need included are the product mix cells B3:F4 and the total profit cell F16. As many factors will eventually be added or taken away I need it to be dynamic. I have managed to do this myself with a constant product mix and so I know it is possible. However, I am struggling at finding a way to use a varied product mix.

Originally Posted by Richard Buttrey

..as I said perhaps you'd better upload the workbook with some examples.
From what you're saying their are constraints on some variables, or some which may be a key variable since they're limited.

I have uploaded a picture, hope this helps