Hi All,
First of all, I am new to the Excel Forum, and have a problem to start out with.
I am having some trouble with getting VBA to run the solver. I have already gone through adding the solver add-in as well as checking solver in VBA References.
I would like solver to run multiple times, while after each time solver runs, I want to increase the row count for all goals and constraints.
I am encountering an error message: "Run-time error '438': Object doesn't support this property or method" on my SolverOK line.
Any suggestions?
Thanks!
Sub solveAll()
'
' Macro2 Macro
'
'
Dim cellChange As Range
Dim cellGoal As Range
Dim cellConstraint As Range
Dim cellConstraintTotalPayed1 As Range
Dim cellConstraintTotalPayed2 As Range
Dim cellConstraintTotalPayed3 As Range
Dim cellConstraintIntCharged1 As Range
Dim cellConstraintIntCharged2 As Range
Dim cellConstraintIntCharged3 As Range
Set cellChange = ActiveSheet.Range("S17:U17")
Set cellGoal = ActiveSheet.Range("V18")
Set cellConstraintTotalPayed1 = ActiveSheet.Range("S17")
Set cellConstraintTotalPayed2 = ActiveSheet.Range("T17")
Set cellConstraintTotalPayed3 = ActiveSheet.Range("U17")
Set cellConstraintIntCharged1 = ActiveSheet.Range("P17")
Set cellConstraintIntCharged2 = ActiveSheet.Range("Q17")
Set cellConstraintIntCharged3 = ActiveSheet.Range("R17")
Set cellConstraintMonthlyMax = ActiveSheet.Range("W17")
Do
SolverReset
SolverOK SetCell:=cellGoal.Address(True, True), MaxMinVal:=2, ByChange:=cellChange.Adress(True, True)
SolverAdd CellRef:=cellConstraintMonthlyMax.Adress(True, True), Relation:=1, FormulaText:="$N$5"
SolverAdd CellRef:=cellConstraintTotalPayed1.Adress(True, True), Relation:=3, FormulaText:=cellConstraintIntCharged1.Adress(True, True)
SolverAdd CellRef:=cellConstraintTotalPayed2.Adress(True, True), Relation:=3, FormulaText:=cellConstraintIntCharged2.Adress(True, True)
SolverAdd CellRef:=cellConstraintTotalPayed3.Adress(True, True), Relation:=3, FormulaText:=cellConstraintIntCharged3.Adress(True, True)
Solver.SolverSolve UserFinish:=True
Set cellChange = cellChange.Offset(0, 1)
Set cellGoal = cellGoal.Offset(0, 1)
Set cellConstraintTotalPayed1 = cellConstraintTotalPayed1.Offset(0, 1)
Set cellConstraintTotalPayed2 = cellConstraintTotalPayed2.Offset(0, 1)
Set cellConstraintTotalPayed3 = cellConstraintTotalPayed3.Offset(0, 1)
Set cellConstraintIntCharged1 = cellConstraintIntCharged1.Offset(0, 1)
Set cellConstraintIntCharged2 = cellConstraintIntCharged2.Offset(0, 1)
Set cellConstraintIntCharged3 = cellConstraintIntCharged3.Offset(0, 1)
Set cellConstraintMonthlyMax = cellConstraintMonthlyMax.Offset(0, 1)
Loop While Trim(cellGoal.Text) > "9"
End Sub
Bookmarks