Solver, Macros and Range Names

  • Thread starter Thread starter Nick
  • Start date Start date
N

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
 
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
 
Back
Top