Dear Excel forum,
I'm trying to automate the excel solver. One of the constraints should change when the solver is unable to find a solution, the next iteration is less strict. In other words, when solver result is less than or equal to 3, the solver found a solution and the loop will end. But when solver result is greater than 3 (one of the constraints is not satisfied, ...), the iteration will start again. Only difference now is that the variable 'serviceL' should decrease with 0,1. And so on until a feasible solution is found. The only problem is the that the loop doesn't stop. Can someone help me? It's probably something simple... Many thanks for your help!
I use the following code:
Dim serviceL As Integer
Dim count As Integer
serviceL = 1
count = 0
Do
serviceL = serviceL - count
count = count + 0.1
' reset
Application.Run "Solver.xlam!SolverReset"
'set up new analysis
Application.Run "Solver.xlam!SolverOk", "$AC$44", 2, 0, ChangeCells, 1, "GRG Nonlinear"
' Other constraints are left out to shorten this thread.
' Constraints that will change
Application.Run "Solver.xlam!SolverAdd", "$V$64", 3, "$L$33*serviceL"
Application.Run "Solver.xlam!SolverAdd", "$V$75", 3, "$U$43*serviceL"
' run the analysis
result = Application.Run("Solver.xlam!SolverSolve", True)
' finish the analysis
Application.Run "Solver.xlam!SolverFinish"
Loop While result > 3
MsgBox "Solver found a solution. Service level of executed maintenance is:" & serviceL".", vbInformation, "Solution found"
Regards,
Tv2012
Bookmarks