Solver, Macros and Range Names

[Nick]

Hello,

I have been running a Goal Seek macro and using range
names instead of cell references. I now want to change
the goal seek macro to a Solver macro but when i record
the macro it will not let me change the macro cell
references to the range names.

Is it possible to change this?

Thanks
Nick

 

[Tushar Mehta]

Well, you can always make the substitution by hand. Suppose you record

SolverOk SetCell:="$B$2", MaxMinVal:=2, ValueOf:="0", ByChange:="$B
$1"
SolverAdd CellRef:="$B$1", Relation:=1, FormulaText:="0"
SolverSolve

where B1 is named x and B2 is named y. Then, change the code to:
solverreset
SolverOk SetCell:="y", MaxMinVal:=2, ValueOf:="0", ByChange:="x"
SolverAdd CellRef:="x", Relation:=1, FormulaText:="0"
SolverSolve True
solverfinish True

The other Solver_ statements are for 'clean up.'

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions