loop for SolverAdd
loop for SolverAdd
(OP)
I need a Loop for SolverAdd.. Is it possible?
I tried this:
Dim h As Integer 'Número de restrições
Dim w As Integer 'Referência para o Loop
Dim q As String 'Referência para localizar célula que deve ser lida
h = Plan1.Range("T8") - 1
q = 7 + Range("E2")
For w = 1 To h
SolverAdd CellRef:="$I$" & q, Relation:="$I$" & q + 1, FormulaText:="$I$" & q + 2
q = q + 5 + Range("E2")
Next w
But this is a incompatible type.
Please help me.
I tried this:
Dim h As Integer 'Número de restrições
Dim w As Integer 'Referência para o Loop
Dim q As String 'Referência para localizar célula que deve ser lida
h = Plan1.Range("T8") - 1
q = 7 + Range("E2")
For w = 1 To h
SolverAdd CellRef:="$I$" & q, Relation:="$I$" & q + 1, FormulaText:="$I$" & q + 2
q = q + 5 + Range("E2")
Next w
But this is a incompatible type.
Please help me.





RE: loop for SolverAdd
Relation must be an integer. Try:
Dim Reltn as long
Reltn = RAnge("$I$" & q + 1).value
For w = 1 To h
SolverAdd CellRef:="$I$" & q, Relation:= Reltn, FormulaText:="$I$" & q + 2
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: loop for SolverAdd
just missed it:
Dim Reltn as long
Reltn = RAnge("$I$" & q + 1).value
For w = 1 To h
SolverAdd CellRef:="$I$" & q, Relation:="" & Reltn, FormulaText:="$I$" & q + 2
really thanks
RE: loop for SolverAdd
Dim n As Integer ' Procura a última linha preenchida das variáveis
n = Plan1.Range("B65000").End(xlUp).Row
'Seta os parâmetros iniciais
SolverOk SetCell:=Range("$E$5").Value, MaxMinVal:=Range("$T$4").Value, ValueOf:=0, ByChange:=Range("$E$8:$E$" & n).Value, _
Engine:=2, EngineDesc:="Simplex LP"
There is no error. But nothing happens.
It just show the restrictions in the windows of Solver.
RE: loop for SolverAdd
RE: loop for SolverAdd
Look the complete comand:
Sub Solver()
Dim n As Integer 'Last line
n = Plan1.Range("B65000").End(xlUp).Row
SolverOk SetCell:="$E$5", MaxMinVal:="$T$4", ValueOf:=0, ByChange:="$E$8:$E$" & n, _
Engine:=2, EngineDesc:="Simplex LP"
'#########################################################################################
Dim h As Integer 'Número de restrições
Dim w As Integer 'Referência para o Loop
Dim q As String 'Referência para localizar célula que deve ser lida
Dim R As Long
h = Plan1.Range("T8") - 1
q = 7 + Plan1.Range("E2")
For w = 1 To h
R = Range("$I$" & q + 1).Value
SolverAdd CellRef:="$I$" & q, Relation:="" & R, FormulaText:="$I$" & q + 2
q = q + 5 + Plan1.Range("E2")
Next w
'##########################################################################################
'
SolverSolve True
'
SolverFinish KeepFinal:=1, ReportArray:=1, OutlineReports:=1
End Sub
RE: loop for SolverAdd
For what it's worth, here is the code I actually used:
Sub Solver()
SolverOk SetCell:="$B$5", MaxMinVal:="$B$9", ValueOf:=0, ByChange:="$b$" & 5, _
Engine:=1, EngineDesc:="GRG Nonlinear"
'#########################################################################################
Dim h As Integer 'Número de restrições
Dim w As Integer 'Referência para o Loop
Dim q As String 'Referência para localizar célula que deve ser lida
Dim R As Long
h = Range("B10") - 1
q = 7 + Range("B11")
For w = 1 To h
R = (Range("$B$" & q).Value - 3)
SolverAdd CellRef:="$B$" & q, Relation:="" & R, FormulaText:="$B$" & q + 2
q = q + 5 ' + Range("E2")
Next w
'##########################################################################################
'
SolverSolve True
'
SolverFinish KeepFinal:=1, ReportArray:=1, OutlineReports:=1
End Sub
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: loop for SolverAdd
I realized the solver parameters are only filled restrictions when I run it.. the other parameters are blank.
Is it possible that some reference is not checked?
RE: loop for SolverAdd
Is it possible to post your spreadsheet?
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: loop for SolverAdd
spreadsheet:
http://sdrv.ms/QO8wLA
It's just a prototype.
I'll improve it.
I'm Brazilian, so the spreadsheet is in portuguese.. ^^
RE: loop for SolverAdd
Dim ChangeAddr As String, MaxMinVal As Long
n = Plan1.Range("B65000").End(xlUp).Row
ChangeAddr = "$E$8:$E$" & n
MaxMinVal = Range("T4").Value
'Seta os parâmetros iniciais
SolverOk SetCell:="$E$5", MaxMinVal:="" & MaxMinVal, ValueOf:=0, ByChange:="" & ChangeAddr, _
Engine:=2, EngineDesc:="Simplex LP"
It seems that if we want to define anything with a string formula we have to create a variable, then use :="" & VariableName
I don't know if that is the only or the best way to do it, but it seems to work.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: loop for SolverAdd
You are the man.
I had already tried something like this .. but I must have written something wrong.
Thank you so much.