Macro to loop solver, save results, and skip when there is no feasible solution

I have a spreadsheet where I need to run multiple solver scenarios. I would like to run a solver loop on varying iterations subject to my constraints. The problem becomes that most of the time solver does not come up with a feasible solution. Sometimes it does though.

Is there a macro that can run my iterations and saves all the feasible solutions for each iteration while moving on to the next iteration if there is no feasible solution? When I write “saves the feasible solution,” I mean that I can go back when it’s complete to see what “Changing Cells” and iterations were used that generated a feasible solution.

This way, I can kick off the macro, walk away for a few hours and come back to see all iterations completed, which would include a few feasible solutions. I guess the feasible solutions could be saved anywhere - new sheet or empty cells in the current sheet.

I have attached a dummy spreadsheet that has the gist of what I’m trying to do. Please don’t worry about the formulas. They were created just so a similar solver problem could be constructed in this dummy spreadsheet based on what I’m really working on. The formulas pull from the solver data, but don’t mean anything.

Solver Inputs:
Objective: Maximize Cell AN131
Changing Cells: H4:H5
Constraint: H1 >=3
Constraint: H2 >=7
Constraint: H4:H5 = integers ranging from 1-99
Constraint: AN132 > 0.14
Constraint: AN133 > 0.01
Solving Method: Evolutionary

The iterations I need to run are in cells G6 and G8. G6 should have a value of either 0 or 1. G8 should be integers of 1-10. So there will be 20 combinations… G6=0, G8=1; G6=1, G8=1; G6=0, G8=2; G6=1, G8=2; G6=0, G8=3; etc. (In reality, there are more combinations)

I’m running Excel 2010. If there is any information I’ve omitted that is needed, my apologies.

Each solver run takes about 5 minutes to run and knowing what’s ahead of me beyond this sheet, I’m guessing the entire project will take in excess of 30 hours. So… any help would be greatly appreciated!!!

If there is a better way to do this, I’m all ears. Thanks!

Not sure I understands you problem. You are talking about cells G6 and G8 but these cells are not included in the Solver setup. There solver may only change cells H4 and H5.

You are running the "Evolutionary" engine, have you tried to use the "GRG-nonlinear" engine?

To see what happened when H4 and H5 changed value I ran this macro that changed H4 and H5 from 1 to 99 in step of 1 and pasted the values from range AN131:AN133 to Sheet2.

Please Login or Register  to view this content.

Looking at the results I could see no change in the AN131:AN133 values.

Alf

Thank you for the reply! To address your points:

-"You are talking about cells G6 and G8 but these cells are not included in the Solver setup" ---> Correct. They are however included in the formulas that are ultimately used to maximize AN131 as well as to meet the constraints of other cells. I'm wanting to run the solver (in this example 20 times) based on how G6 and G8 affect the formulas in the sheet. For each combination of G6 and G8, I would like to run the solver separately to find what values for the changing cells H4:H5 are most optimal. In other words....

1. FIRST have G6=0, G8=1, run solver, paste the changing cells value and objective cell value to empty cells somewhere (if a feasible solution is found; if no feasible solution, nothing is pasted);
2. NEXT set G6=1, G8=1, run solver, paste the changing cells value and objective cell value to empty cells somewhere;
3. NEXT set G6=0, G8=2, run solver, paste the changing cells value and objective cell value to empty cells somewhere;
4. NEXT set G6=1, G8=2, run solver, paste the changing cells value and objective cell value to empty cells somewhere;
5. Repeat until all iterations are complete *regardless if a feasible solution is found or not*

-You are running the "Evolutionary" engine ---> Correct. I originally had it set to GRG Nonlinearly, but for some reason, errors were being generated once I added integer constraints. When I switched to Evolutionary, there were no more errors and my integer constraints were satisified.

-Looking at the results I could see no change in the AN131:AN133 values. ---> I'm sure you're correct. This spreadsheet is a dumbed down version of my original spreadsheet. I don't care about the results in this sheet. I'm hoping to come away with a macro to loop the solver as described above and below (I can make minor adjustments in the macro to make sure the correct cells are being referenced). Rest assured, the real spreadsheet values change frequently. The data I'm working with is sensitive, which is why I created this dummy sheet.

Basically what I'm trying to code is ---> run solver, paste results somewhere if a feasible solution is found, change G6 and G8 for the next iteration, run solver, paste results somewhere if a feasible solution is found, change G6 and G8 for the next iteration, run solver, etc., etc.

My sincere apologies if my original post was not explained well. I hope this provides clarity. And again, thank you for taking your valuable time to consider my problem.

One of the problem with the Excel solver is that it is not that powerful. Depending a bit on your problem there is the possibility to download the most advanced model and use it for two weeks free of charge. As the makers of solver are most eager to sell their products their help desk are most helpful as opposed to model problem when running the Excel solver. This is the product sold to Microsoft and most of the time they do refer to Microsoft if you need help with Solver models.

There is also the possibility of getting a advanced and free model if you are a student. I'm adding the link to the makers of Solver.

http://www.solver.com/

Perhaps it would be possible to run solver in a loop with some cut-off time that is a solution is not found after a certain time solver should start new run so you could let this run over night as the full run should take 30+ hours.

I once had a solver problem where I wanted to change range setting for solver and got this tip from the "Solver.com" help desk.

Function showTRial(Reason As Integer)
showTRial = 0
'return 0 if we want to stop, 1 if we want to continue and 2 if we want to continue the solution process
End Function

'Sub myfb()
'SolverOptions MaxTime:=9
'SolverSolve UserFinish:=True, ShowRef:="showTRial"
'SolverFinish KeepFinal:=1
'End Sub

But I never managed to get it working properly.

If you are looking for a VBA code to run solver in a loop perhaps this could be of help (post #11). Macro based on OP's uploaded file


https://www.excelforum.com/excel-pro...ml#post4673650


Alf

Alf - Thank you for your time and suggestion of the advanced solver. I will also check out post #11 of the link you provided. Perhaps I can dumb down the loop and hard program in VBA to change cells G6 and G8. I could perhaps just write every iteration into solver. That wouldn't be too bad. It would essentially be a very long piece of the same code but changed only for the iterations... and where to paste the values at.

I will mark this thread closed. Again, thank you.