how to use Solver in Excel VBA
Hi all,
I'm trying to use solver to minimize a cell for a least squares regression. The minimization cell is a weighted sum of squared residuals. Three variables exist that define the regression line. Another six cells define the bounds for the variables.
I get the following error:
Compile Error: Sub or Function not defined
Here is the code:
Sub WLS_VARIO()
'
Range("F14").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/1.1"
ActiveCell.Offset(1, 0).FormulaR1C1 = "=RC[-2]/1.1"
ActiveCell.Offset(2, 0).FormulaR1C1 = "=RC[-2]/1.1"
ActiveCell.Offset(0, 1).FormulaR1C1 = "=RC[-3]*1.1"
ActiveCell.Offset(1, 1).FormulaR1C1 = "=RC[-3]*1.1"
ActiveCell.Offset(2, 1).FormulaR1C1 = "=RC[-3]*1.1"
Range("F14:G16").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'
SolverReset
SolverOk SetCell:="$D$12", MaxMinVal:=2, ValueOf:=0, ByChange:="$D$14:$D$16", _
Engine:=3, EngineDesc:="Evolutionary"
SolverAdd CellRef:="$D$14", Relation:=1, FormulaText:="$G$14"
SolverAdd CellRef:="$D$15", Relation:=1, FormulaText:="$G$15"
SolverAdd CellRef:="$D$16", Relation:=1, FormulaText:="$G$16"
SolverAdd CellRef:="$D$14", Relation:=3, FormulaText:="$F$14"
SolverAdd CellRef:="$D$15", Relation:=3, FormulaText:="$F$15"
SolverAdd CellRef:="$D$16", Relation:=3, FormulaText:="$F$16"
SolverSolve
End Sub
Any help please?
|