L
Lone
Hi,
I'm using Microsoft Excel 2003, and try to use Macro to call solver. But I
observed that the result not 100% correct. After I checked, I found the
constraint for integer forcing has not been added (the forth constraint).
Please help me to solve this problem.
My second question is how to add the code for user add-in the Solver.xla
automatically?
============================================
Sub SolverMacro()
'
icol = Sheets("Simulation").[l1].CurrentRegion.Columns.Count
irow = Sheets("Simulation").[A1].CurrentRegion.Rows.Count
SolverReset
SolverAdd CellRef:=Range(Cells(9, 10), Cells(irow, 10)), Relation:=3,
FormulaText:="0"
SolverAdd CellRef:=Range(Cells(8, 12), Cells(8, icol)), Relation:=1,
FormulaText:=Range(Cells(2, 12), Cells(2, icol))
SolverAdd CellRef:=Range(Cells(8, 12), Cells(8, icol)), Relation:=3,
FormulaText:=Range(Cells(6, 12), Cells(6, icol))
‘ THIS ONE NOT WORK
SolverAdd CellRef:=Range(Cells(8, 12), Cells(8, icol)), Relation:=4,
FormulaText:="integer"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=True
SolverOk SetCell:="$F$6", MaxMinVal:=1, ValueOf:="0",
ByChange:=Range(Cells(8, 12), Cells(8, icol))
SolverSolve(True)
End Sub
I'm using Microsoft Excel 2003, and try to use Macro to call solver. But I
observed that the result not 100% correct. After I checked, I found the
constraint for integer forcing has not been added (the forth constraint).
Please help me to solve this problem.
My second question is how to add the code for user add-in the Solver.xla
automatically?
============================================
Sub SolverMacro()
'
icol = Sheets("Simulation").[l1].CurrentRegion.Columns.Count
irow = Sheets("Simulation").[A1].CurrentRegion.Rows.Count
SolverReset
SolverAdd CellRef:=Range(Cells(9, 10), Cells(irow, 10)), Relation:=3,
FormulaText:="0"
SolverAdd CellRef:=Range(Cells(8, 12), Cells(8, icol)), Relation:=1,
FormulaText:=Range(Cells(2, 12), Cells(2, icol))
SolverAdd CellRef:=Range(Cells(8, 12), Cells(8, icol)), Relation:=3,
FormulaText:=Range(Cells(6, 12), Cells(6, icol))
‘ THIS ONE NOT WORK
SolverAdd CellRef:=Range(Cells(8, 12), Cells(8, icol)), Relation:=4,
FormulaText:="integer"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=True
SolverOk SetCell:="$F$6", MaxMinVal:=1, ValueOf:="0",
ByChange:=Range(Cells(8, 12), Cells(8, icol))
SolverSolve(True)
End Sub