Looping solver for recursive optimization

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.
 
G

Graham Whitehead

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
 
B

brian.wile

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

brian.wile

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

Pflugs

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top