Solver

For more complicated situations you should use the Solver add-in which can handle several dependent cells and can also take certain conditions into consideration.
You must have the Solver add-in installed first.

and the goal of the optimisations (parameter MaxMinVal)
SolverSolve carries the optimisations.
After the optimisation, the dialog box can be displayed using the SendKeys and then executed.

supports recorded macros
created by solver.com

SolverReset

SolverAdd

SolverAdd CellRef:=$A$1, Relation:=2, FormulaText="some text" 

SolverOptions

SolverOptions sets the options of the Solver.

SolverOK

SolverOK specifies the cells to which the solver is to be applied

SolverFinish

SolverChange

SolverSolve

SolverSolve(True)