using solver an writing values

Question

I am not very experienced in VBA but I have a basic understanding of it. I am trying to create an optimization model where Solver generates minimum values for a set of constraints:

-Cell O40 needs to be minimized, and that value recorded and printed in a cell Kx -Cell O41 is my dynamic constraint, where it needs to be set equal to the value in cell Jx for each iteration of the solver. -Cells J40:J45 are weights which will be changing, and subject to the constraint J46=1 (where J46=SUM(J40:J45) such that the weights add up to 1)

I ran Solver once and recorded a macro and got the following output:

Sub Solve()

SolverAdd CellRef:="$J$46", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$O$41", Relation:=2, FormulaText:="$J17"
SolverOk SetCell:="$O$40", MaxMinVal:=2, ValueOf:=0, ByChange:="$J$40:$J$45", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve

End Sub

Now I need the Solver to do two things:

Write the value that is solved for in cell O40 into the cell Kx, at the end of the routine, where cell Kx corresponds to the adjoining cell Jx that determined the fixed value constraint of cell O41.
Repeat the process for the range J17:J33, changing the fixed value constraint in cell O41.

The result should be that the values in the cells K17:K33 are filled in and minimized for a given level in cells J17:J33.

I appreciate any help I can get.

You have two problems: solving optimization problems and programming. Your question feels too broad for SO. — duffymo
Im sure that a few lines of code that specify the range of cells I need to change and some sort of counter to offset the cell after each iteration should do it, but this is what I need help with. I can do it manually but that is very inefficient for what need to do. — fede
Interesting question, I would guess you need to write up a vba macro according to your specification above. It is not that hard now when you have described the procedure in detail. — Mats Lind

Clauric Clauric · Accepted Answer · 2016-08-31T10:51:08

I think that the best way to do the recursion is to do a While loop, something like this:

Dim a as Variant 
a = 17

While a <= 33  
  'Solver functionality in here   
   a = a + 1 
Wend

This will cover the first part of your problem. The next issue is to keep your solver references constant. In this case, I would have your values in J17:J33 in a separate location, and move the values into the relevant cells when needed. This would mean that after every solution, you would copy the answer suggested to another location, and bring in new information for the next solver attempt.

using solver an writing values

1 Answers