loop for SolverAdd

[guilhermepedro]

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.

 

[IDS]

See

http://msdn.microsoft.com/en-us/library/office/ff838657.aspx

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/

 

[guilhermepedro]

thanks dude..

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

 

[guilhermepedro]

now.. I have a problem with SolverOk

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.

 

[Denial]

I think you need to call SolverSolve after you have called SolverOK.

 

[guilhermepedro]

Yes.. i did

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

 

[IDS]

I copied and pasted that code, and made some minor modifications to work on a simple problem (solving a quadratic equation), and it worked without any problem.

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/

 

[guilhermepedro]

So strange.

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?

 

[IDS]

What version of Excel are you using? (I'm using 2010).

Is it possible to post your spreadsheet?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[guilhermepedro]

Me too.

spreadsheet:

http://sdrv.ms/QO8wLA

It's just a prototype.
I'll improve it.

I'm Brazilian, so the spreadsheet is in portuguese.. ^^

 

[IDS]

This seems to work:

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/

 

[guilhermepedro]

Really thanks.

You are the man.

I had already tried something like this .. but I must have written something wrong.

Thank you so much.