Automating Solver

[Karthik_kk25]

Hello everyone

I am looking for solutions to automate solver process.

I have to allocate quantities between two production facilities such that I minimize costs. While I know how to do that using solver, what I need help in is replicating this for multiple scenarios.

I have ten different different scenarios so the constrains change everytime. instead of framing constraints every time, is it possible to automate solver?

 

[Kenneth Hobson]

'https://msdn.microsoft.com/en-us/library/office/ff838657.aspx
'Tools > References > Solver  'Must enable Solver add-in first, then add reference.
Sub mSolver(sSetCell$, sByChange$, sFormulaText$)
  SolverReset
  SolverOk SetCell:=sSetCell, MaxMinVal:=1, ValueOf:=0, ByChange:=sByChange, Engine:=1 _
    , EngineDesc:="GRG Nonlinear"
  'Relation=2=equals
  SolverAdd CellRef:=sSetCell, Relation:=2, FormulaText:=sFormulaText
  ' Do not display the Solver Results dialog box?
  SolverSolve UserFinish:=True  'True=do not show, False=do show.
End Sub

 

[Karthik_kk25]

Sorry Kenneth
I did not understand the code. I am pretty new to VBA.
I have attached a sample file. In this excel, c4:d10 gives me freight values from facility 1 and facility 2 to each location. The demand for each location is in column E. The capacity of each facility is in c11 and d11.
I need to allocate qty to each location from both the facilities.

My difficulty is i have scenarios where the demand alone changes. that is column E changes.
How to modify the code for that?

 

[Kenneth Hobson]

Solver has many ways to use it. I can only help you with the automation.

You have to decide what inputs it needs. A manually recorded macro is usually the best place to start. I would suggest doing a few manually, write down what you did, then record one macro to show one run. Then, automation can take care of multiple runs with varied inputs. As you can see in mine, it only needs 3 inputs. Well 3 for what I was doing. I set the options in it as those did not change.

Maybe even Screen snips as you solve run one task might help. Snipping Tool or OneNote (Win+S or Win+Shift+S) are good snipping tools. A run of OneNote once enables the hotkeys listed.

 

[Hui]

Solver also has the facility to save it's setup to a Range
You can do this manually for each problem
Then setup a macro to load each one in turn, run the solver, save the solution and repeat