Use a cell reference to set Solver objective value

Question

Hello, I'm trying to create a surface plot in Excel using values generated from Solver. The y variable on my plot is a range of objective values that I'd like Solver to target. I'm trying to record a macro to run 2 nested loops--one to input a range of x variables, and one within that loop to run my range of y values. Is it possible to get Solver run using my range of y values as objective values for each iteration? Alternatively, if there isn't a way to set the objective value to the value in a cell, is there another way to use a macro to run Solver through iterations with different objective values? Thanks.

Answer 1

I don't know off-hand, since I usually set up new rather than repeated. It may make sense to clear all three cells prior to starting, if the solutions are far apart. Since the OP was looking at a surface, that may not be an issue.

Answer 2

Bernie wrote:

For Each X In Range("D8:D17")

For Each Y In Range("E7:K7")

Range("D1").Value = X.Value

SolverOk SetCell:="$D$3", MaxMinVal:=3, ValueOf:=Y.Value, ByChange:="$D$2"

SolverSolve True

Cells(X.Row, Y.Column).Value = Range("D2").Value

Next Y

Next X

Bernie, would it be prudent to add Range("D3").ClearContents at the beginning of the inner loop?

[ERRATA] I meant the by-changing cell, not the objective cell.  So Range("D2").ClearContents.  All references to "target cell" below should be "by-changing cell".

When I use Solver manually, it seems that (sometimes?) it uses any value in the target cell as an initial value for its algorithm.  Or more accurately:  any value in the target cell seems to impact the behavior of Solver.

That might depend on the Solver method.  But for some set-ups, I do get different values depending on whether the target cell is empty or it has a value (e.g. from a previous run of Solver).

[EDIT] But that might be the case only when the Solver parameters are not changed between runs.  I have not done much to investigate this observation.  I usually simply clear the target cell "autonomically" before using Solver.

Answer 3

Of course, though you left out a lot of information. For example, with a formula that references D1 and D2 in D3, with E7:K7 containing the target values, and D8:D17 containing your other variable....

Put this formula in D3

=D2*D1

and fill E7:K7 and D8:D17 with integers, and run this - which requires a reference to SOLVER in your VBA project:

Sub SolverMacro()

Dim X As Range

Dim Y As Range

For Each X In Range("D8:D17")

For Each Y In Range("E7:K7")

Range("D1").Value = X.Value

SolverOk SetCell:="$D$3", MaxMinVal:=3, ValueOf:=Y.Value, ByChange:="$D$2"

SolverSolve True

Cells(X.Row, Y.Column).Value = Range("D2").Value

Next Y

Next X

End Sub