Hi ,
I wrote a code to run solver , unfortunately after 10 loops the results are not optimal.
This is the code :
Sub Solver()
Dim Pro As Range
Dim Dem As Range
Dim Tot_Pro As Range
Dim Start_Row As Integer
Dim End_Row As Integer
Dim i As Integer
Dim j As Integer
Application.ScreenUpdating = False
Range("D7:F9999").Value = 0
'Loop for runnnig solver on rows
Start_Row = Range("B3")
SolverOptions Precision:=0.000001
Worksheets("Sim").Activate
For i = 1 To 10
SolverReset
End_Row = Start_Row + 20
Set Pro = Range(Cells(Start_Row, 4), Cells(End_Row, 6))
Set Dem = Range(Cells(Start_Row, 3), Cells(End_Row, 3))
Set Tot_Pro = Range(Cells(Start_Row, 7), Cells(End_Row, 7))
SolverOK SetCell:=Range("b5"), _
MaxMinVal:=2, ByChange:=Pro.Address
'SolverAdd cellRef:=Pro.Address, relation:=4 'Integer takes too long
SolverAdd cellRef:=Pro.Address, relation:=3, FormulaText:=0
SolverAdd cellRef:=Tot_Pro.Address, relation:=3, FormulaText:=Dem.Address
SolverAdd cellRef:=Range("D3:f3"), relation:=3, FormulaText:=0
SolverSolve userfinish:=True
Start_Row = End_Row
Next i
'Round numbers loop
j = 4
For j = 4 To 6
i = 7
For i = 7 To End_Row
Cells(i, j) = Application.WorksheetFunction.Round(Cells(i, j), 0)
Next i
Next j
Application.ScreenUpdating = True
End Sub
When I check the results for lines 240+ I see that manually I can receive better results. Looks like the solver engine collapses or something like that.
Any Ideas how to make it work for more loops? I need to run from i =1 to 20 +-
Thanks
I wrote a code to run solver , unfortunately after 10 loops the results are not optimal.
This is the code :
Sub Solver()
Dim Pro As Range
Dim Dem As Range
Dim Tot_Pro As Range
Dim Start_Row As Integer
Dim End_Row As Integer
Dim i As Integer
Dim j As Integer
Application.ScreenUpdating = False
Range("D7:F9999").Value = 0
'Loop for runnnig solver on rows
Start_Row = Range("B3")
SolverOptions Precision:=0.000001
Worksheets("Sim").Activate
For i = 1 To 10
SolverReset
End_Row = Start_Row + 20
Set Pro = Range(Cells(Start_Row, 4), Cells(End_Row, 6))
Set Dem = Range(Cells(Start_Row, 3), Cells(End_Row, 3))
Set Tot_Pro = Range(Cells(Start_Row, 7), Cells(End_Row, 7))
SolverOK SetCell:=Range("b5"), _
MaxMinVal:=2, ByChange:=Pro.Address
'SolverAdd cellRef:=Pro.Address, relation:=4 'Integer takes too long
SolverAdd cellRef:=Pro.Address, relation:=3, FormulaText:=0
SolverAdd cellRef:=Tot_Pro.Address, relation:=3, FormulaText:=Dem.Address
SolverAdd cellRef:=Range("D3:f3"), relation:=3, FormulaText:=0
SolverSolve userfinish:=True
Start_Row = End_Row
Next i
'Round numbers loop
j = 4
For j = 4 To 6
i = 7
For i = 7 To End_Row
Cells(i, j) = Application.WorksheetFunction.Round(Cells(i, j), 0)
Next i
Next j
Application.ScreenUpdating = True
End Sub
When I check the results for lines 240+ I see that manually I can receive better results. Looks like the solver engine collapses or something like that.
Any Ideas how to make it work for more loops? I need to run from i =1 to 20 +-
Thanks