Excel Solver Named Ranges in VBA

Question

Is it possible to amend the vba script so that in defining the parameters for 'Solver' I can used the 'Names' for the various ranges instead of the absolute cell references?

In the example below the various ranges are defined in the spreadsheet as;

$A$1 ..... "obj_func"
$E$8:$E$11,$I$8:$M$11 ..... "bin_var", "qty_cut"
$C$8:$C$11 ..... "qty_used"



SolverOk SetCell:="$A$1", MaxMinVal:=2, ValueOf:=0, ByChange:= _
"$E$8:$E$11,$I$8:$M$11", Engine:=2, EngineDesc:="Simplex LP"

SolverAdd CellRef:="$C$8:$C$11", Relation:=1, FormulaText:="stock_lengths"

Gary's Student · Accepted Answer · 2015-05-23 21:52:25Z

1

UNTESTED

Try replacing:

SolverAdd CellRef:="$C$8:$C$11", Relation:=1, FormulaText:="stock_lengths"

with:

SolverAdd CellRef:=Range("qty_used").Address, Relation:=1, FormulaText:="stock_lengths"

answered May 23, 2015 at 21:52

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel Solver Named Ranges in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related