×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

loop for SolverAdd

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.

RE: loop for SolverAdd

(OP)
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

RE: loop for SolverAdd

(OP)
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.

RE: loop for SolverAdd

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

RE: loop for SolverAdd

(OP)
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

RE: loop for SolverAdd

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/

RE: loop for SolverAdd

(OP)
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?

RE: loop for SolverAdd

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/

RE: loop for SolverAdd

(OP)
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.. ^^

RE: loop for SolverAdd

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/

RE: loop for SolverAdd

(OP)
Really thanks.

You are the man.

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

Thank you so much.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources