B
brian.wile
I have an optimization problem that is broken down into a number of
smaller optimization problems given the number of observations on a
variable. On each observation, I would like to have solver change a
range of two cells to minimize the a third cell. This would be looped
over the entire range of data given a starting reference. The problem
I am having though is that at every observation I get a break "Code
Execution Has Been Interrupted". Here is the code I have so far:
Private Sub CommandButton1_Click()
Dim cell1 As Range
Dim cell2 As Range
Dim cell3 As Range
Dim rindex As Integer
Dim colindex As Integer
Dim i As Integer
Dim nobs As Integer
Set cell1 = Range("O13")
rindex = cell1.Row
colindex = cell1.Column
nobs = Cells(Rows.Count, "O").End(xlUp).Row
Application.DisplayAlerts = False
For i = 1 To (nobs - 13)
Set cell1 = Cells(rindex + (i - 1), colindex)
Set cell2 = Cells(rindex + (i - 1), colindex + 1)
Set cell3 = Cells(rindex + (i - 1), colindex + 3)
SOLVER.SolverReset
SOLVER.SolverOptions MaxTime:=100, Iterations:=1000,_
Precision:=0.000001,Convergence:=0.000001
SOLVER.SolverOk setcell:=cell3.Address, maxminval:=2,_
bychange:=Range(cell1, cell2).Address
SOLVER.SolverSolve userfinish:=True
Next i
End Sub
The break occurs at SOLVER.SolverSolve userfinish:=True. Thanks.
smaller optimization problems given the number of observations on a
variable. On each observation, I would like to have solver change a
range of two cells to minimize the a third cell. This would be looped
over the entire range of data given a starting reference. The problem
I am having though is that at every observation I get a break "Code
Execution Has Been Interrupted". Here is the code I have so far:
Private Sub CommandButton1_Click()
Dim cell1 As Range
Dim cell2 As Range
Dim cell3 As Range
Dim rindex As Integer
Dim colindex As Integer
Dim i As Integer
Dim nobs As Integer
Set cell1 = Range("O13")
rindex = cell1.Row
colindex = cell1.Column
nobs = Cells(Rows.Count, "O").End(xlUp).Row
Application.DisplayAlerts = False
For i = 1 To (nobs - 13)
Set cell1 = Cells(rindex + (i - 1), colindex)
Set cell2 = Cells(rindex + (i - 1), colindex + 1)
Set cell3 = Cells(rindex + (i - 1), colindex + 3)
SOLVER.SolverReset
SOLVER.SolverOptions MaxTime:=100, Iterations:=1000,_
Precision:=0.000001,Convergence:=0.000001
SOLVER.SolverOk setcell:=cell3.Address, maxminval:=2,_
bychange:=Range(cell1, cell2).Address
SOLVER.SolverSolve userfinish:=True
Next i
End Sub
The break occurs at SOLVER.SolverSolve userfinish:=True. Thanks.