Hi, I am writing a macro to automatically set up and run the solver add in for excel (I am using excel 2000). When I run the macro however, nothing happens whatsoever to the values in the cells. When I run the solver manually however, it works fine. Can anyone tell me why my macro doesn't work?
This is my macro:
Sub solver_test1()
Worksheets("Calculations").Activate
SolverReset
SolverOk SetCell:="$C$30", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$20"
SolverAdd CellRef:="$F$7", Relation:=2, FormulaText:="$L$7"
SolverAdd CellRef:="$F$7", Relation:=3, FormulaText:=0
SolverAdd CellRef:="$C$27", Relation:=2, FormulaText:=60000
SolverSolve
End Sub
Incidentally, when I open the solver window to run it manually, the three constraints set up in the macro are already entered (ie. F7=L7, F7 >=0, and C27=60000) and the type of solve (ie max, min or equal to value) is already selected. However, the cell references for which cell is the target cell (i.e. C30) and which cell is to be changed (ie. C20) are blank. Putting the command UserFinish:=True/False after SolverSolve has no effect.
Before you ask, I have activated the solver add-in in my excel workbook and referenced it in my VB editor...
Any ideas would be much appreciated.
Thank you excel gurus!
Douglas
This is my macro:
Sub solver_test1()
Worksheets("Calculations").Activate
SolverReset
SolverOk SetCell:="$C$30", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$20"
SolverAdd CellRef:="$F$7", Relation:=2, FormulaText:="$L$7"
SolverAdd CellRef:="$F$7", Relation:=3, FormulaText:=0
SolverAdd CellRef:="$C$27", Relation:=2, FormulaText:=60000
SolverSolve
End Sub
Incidentally, when I open the solver window to run it manually, the three constraints set up in the macro are already entered (ie. F7=L7, F7 >=0, and C27=60000) and the type of solve (ie max, min or equal to value) is already selected. However, the cell references for which cell is the target cell (i.e. C30) and which cell is to be changed (ie. C20) are blank. Putting the command UserFinish:=True/False after SolverSolve has no effect.
Before you ask, I have activated the solver add-in in my excel workbook and referenced it in my VB editor...
Any ideas would be much appreciated.
Thank you excel gurus!
Douglas