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.

Hmm, dont know why you put the extra SOLVER at the beggining - but here is
some code i used for much the same thing and it works fine for me.

'Solve but dont display results
SolverSolve userfinish:=True
'get result
SolverFinish KeepFinal:=1

wrote in message
oups.com...
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.

Thanks for the quick reply. I added SolverFinish KeepFinal:=1 and
removed the additional Solver but I still get the same break.

Graham Whitehead wrote:
Hmm, dont know why you put the extra SOLVER at the beggining - but here is
some code i used for much the same thing and it works fine for me.

'Solve but dont display results
SolverSolve userfinish:=True
'get result
SolverFinish KeepFinal:=1

wrote in message
oups.com...
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.

Thanks for the quick reply. I added SolverFinish KeepFinal:=1 and
removed the additional Solver but I still get the same break.

Graham Whitehead wrote:
Hmm, dont know why you put the extra SOLVER at the beggining - but here is
some code i used for much the same thing and it works fine for me.

'Solve but dont display results
SolverSolve userfinish:=True
'get result
SolverFinish KeepFinal:=1

if you can attach an example sheet with some code i can take a look

wrote in message
oups.com...
Thanks for the quick reply. I added SolverFinish KeepFinal:=1 and
removed the additional Solver but I still get the same break.

Graham Whitehead wrote:
Hmm, dont know why you put the extra SOLVER at the beggining - but here
is
some code i used for much the same thing and it works fine for me.

'Solve but dont display results
SolverSolve userfinish:=True
'get result
SolverFinish KeepFinal:=1

Try restarting your computer when you get that error. It happens to me all
the time after I intentionally break the code.

Your code seems okay to me, and I've done a few routines like this one.

Pflugs

" wrote:

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.

Thanks guys. I reinstalled Excel and it the code works now. Go
figure...

Pflugs wrote:
Try restarting your computer when you get that error. It happens to me all
the time after I intentionally break the code.

Your code seems okay to me, and I've done a few routines like this one.

Pflugs

" wrote:

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.