Looping with solver

[kawhi2]

I have created an optimizer using solver which will output the optimal value, given several constraints, in a particular cell (which is AG1 in my case).
However, I want to write a loop that will iteratively prompt solver to find the next best solution by updating the maximum value constraint to a lower value.

Basically what I have now is:

Please Login or Register  to view this content.

where X is the number iterations and AH1 is the cell containing a decreased value that I want to set as the new maximum constraint, which I will decrease by .01 each time.

However, the problem with this code is that the solver won't actually update any of the spreadsheet cells until after I stop execution, by which the AH1 cell will have decreased .01 times however many iterations it went through before being stopped and produce only that particular solution.

Can someone give me a pointer as to how I can, through a single execution of code: 1) retrieve the solution with all cells in the spreadsheet updated per iteration 2) store/print the results of all iterations in a separate spreadsheet so that I can basically view all solutions?

[Alf]

As you have not given much information about your problem I'm not sure how to help you but perhaps

Please Login or Register  to view this content.

Ms Office 2013? This is not the Mac version by the way as there are problems running solver with a macro.

Alf

[kawhi2]

So, I haven't been working on this for a while, but I've made some changes:

Please Login or Register  to view this content.

The rundown is, I have my solver constraints saved in blocks AE14:AH18 on Sheet1. Each time solver runs, it SHOULD change the values of AJ1:AJ150, and it does so perfectly fine if I run it manually.
I then want to copy each iteration of solution to an individual column on Sheet2, as you can see. The [Range("AH1") = Range("AH1") - 0.01] part of the code modifies a constraint to prompt a new solution each iteration.
The problem is, when I run it inside this loop, I don't think solver is actually changing the values of the AJ1:AJ150 until the last iteration.

After I run the script, I get columns of invalid cell reference errors before the last column, which did successfully paste the solution. Basically, solver only works correctly on the last iteration of the loop.

Are there any pointers as to how I can store multiple solutions of solver in the manner that I described / identify what may be the issue?

[kawhi2]

Anybody who might have an answer?