goncalogera
New Member
- Joined
- Nov 10, 2020
- Messages
- 14
- Office Version
- 2019
- Platform
- Windows
- Mobile
- Web
I've encountered a problem while trying to add a solver function to a userform button.
The solver is running well with the exception that it's not adding a specific constraint. The code for the solver is as follows:
Cell $J$12 is a SUM of cells $K$2:$K$7 which are all the binary constraints.
Since it's a MIN objective solver only one can be equal to 1 and there always has to be a restraint equal to one hence the need of a binary constraint and the one which is not working which is the SUM of all binaries. That constraint has to be binary since this is an optimization problem where the variables are 0 if not chosen and 1 if chosen.
I've seen threads where they suggest using to SolverSolve lines twice but it doesn't work.
The userform button code is as follows:
Thanks for your help in advance!
The solver is running well with the exception that it's not adding a specific constraint. The code for the solver is as follows:
VBA Code:
Sub Solver1()
SolverReset
SolverOk SetCell:="$I$12", MaxMinVal:=2, ByChange:="$K$2:$K$7", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$J$12", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$K$2:$K$7", Relation:=5, FormulaText:="binary"
SolverAdd CellRef:="$L$2:$L$7", Relation:=3, FormulaText:="0"
SolverSolve userfinish:=True
End Sub
Cell $J$12 is a SUM of cells $K$2:$K$7 which are all the binary constraints.
Since it's a MIN objective solver only one can be equal to 1 and there always has to be a restraint equal to one hence the need of a binary constraint and the one which is not working which is the SUM of all binaries. That constraint has to be binary since this is an optimization problem where the variables are 0 if not chosen and 1 if chosen.
I've seen threads where they suggest using to SolverSolve lines twice but it doesn't work.
The userform button code is as follows:
VBA Code:
Private Sub CalculateButton_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Spreads")
sh.Range("K12").Value = Me.MontanteBox.Value
sh.Range("M12").Value = Me.txtStartDate.Value
sh.Range("N12").Value = Me.txtDueDate.Value
Call Module3.Solver1
If sh.Range("K2") = 1 Then
MsgBox "Escolha ótima será o Novo Banco", vbInformation
Exit Sub
End If
If sh.Range("K3") = 1 Then
MsgBox "Escolha ótima será o BCP", vbInformation
Exit Sub
End If
If sh.Range("K4") = 1 Then
MsgBox "Escolha ótima será o BIC", vbInformation
Exit Sub
End If
If sh.Range("K5") = 1 Then
MsgBox "Escolha ótima será a CGD", vbInformation
Exit Sub
End If
If sh.Range("K6") = 1 Then
MsgBox "Escolha ótima será o BPI", vbInformation
Exit Sub
End If
If sh.Range("K7") = 1 Then
MsgBox "Escolha ótima será o DB", vbInformation
Exit Sub
End If
End Sub
Thanks for your help in advance!