VBA code to RUN Solver mutiple times (on different target cells)

[BEETAL]

Can some one help to modefy the following code wherein I can run
the macro on 250 cells across a table/or matrix form.


SolverOk SetCell:="$F$52", MaxMinVal:=3, ValueOf:="0",_ ByChange:="$C$52"
SolverAdd CellRef:="$C$52", Relation:=3, FormulaText:="0"
SolverSolve True
SolverOk SetCell:="$F$53", MaxMinVal:=3, ValueOf:="0",_ ByChange:="$C$53"
SolverAdd CellRef:="$C$53", Relation:=3, FormulaText:="0"
SolverSolve True

end sub
I need to run the solver on coloumns f to k on 20 cells each. Can I use a
range command or offset command
to keep changing the target cells over and over.

Some loop structure would do ,I suppose. Since I have never worked
on this before, I would like a jum- start on solver.

The constraints will be set as shown.

thank you very much for the timely help, in advance.

 

[Jon Peltier]

Not tested, but this should get you started:

Dim iRow As Long, iCol As Long
Dim sSetCell As String, sByChange As String
For iRow = 52 To 152 ' guessing the range
For iCol = 6 to 11 ' columns F through K
sSetCell = Range("A1").Offset(iRow - 1, iCol - 1).Address
sByChange = Range("A1").Offset(iRow - 1, 2).Address
SolverOk SetCell:=sSetCell, MaxMinVal:=3, ValueOf:="0",
ByChange:=sByChange
SolverAdd CellRef:=sByChange, Relation:=3, FormulaText:="0"
SolverSolve True
Next
Next

- Jon

 

[BEETAL]

Dear on,many thanks for the code. The code as modified by me is shown below.
After running through the course of first coloumn from row 52 to 69 ,solver
does not stop. Spreadsheet does not respond. The message that appears at the
left bottom end of the spreadsheet shows setting up..... It seems that the
solver keeps looking for a problem after soving the cell value at 69. Why?
Would you like me to send you the spreadsheet,please advice.

Thanks a lot again.Here is the code .

Worksheets("Jon").Select
Dim iRow As Long, iCol As Long
Dim sSetCell As String, sByChange As String
iCol = 6
'For iCol = 6 To 11 ' columns F through K
For iRow = 52 To 69 ' guessing the range
sSetCell = Range("myrangetrial").Offset(iRow - 52, iCol - 6).Address
sByChange = Range("myrangetrial").Offset(iRow - 52, -3).Address
SolverOk SetCell:=sSetCell, MaxMinVal:=3, ValueOf:="0", ByChange:=sByChange
SolverAdd CellRef:=sByChange, Relation:=3, FormulaText:="0"
SolverSolve True
Next
'Next

End Sub

 

[BEETAL]

Just to add a little more on the problem Jon.

It seems,looking at the Task Manager, I notice that there are "two
instances" of solver running. Is it the reason for the spreadsheet hanging
up. Why there are two instances seen when actually there should be one
appearing.

Please help.

 

[Jon Peltier]

I don't know why the loop doesn't stop when it gets to the upper limit of
the loop. Could you forestall the problem with a fire escape? Insert this
line and see if it stops:

If iRow > 69 Then Exit For

All rows between 52 and 69 are solved by Solver?

- Jon

 

[BEETAL]

Many thanks Jon for the time, yes, solver stops sometimes and I can see a
'First trial solution" comment on the left hand side.
Surprisingly,very annoying though, the following code works and it is no
where as elegant as yours. I just got it by hit-and-trial method.can we
modify this code and make it elegant like yours,this one works though

Worksheets("jon").Select
'For j = 1 To 10' to add for coloumns
For i = 1 To 8

SolverOk SetCell:=Range("myrangetrial").Offset(i - 1, 0).Address, _
MaxMinVal:=3, ValueOf:="0", ByChange:=Range("myrangetrial").Offset(i - 1,
-3).Address
SolverSolve True
Next i
next j ' for coloumns
end sub

I think I need to do the following

a) add one more for loop for more than one column - which I have tried to do

b) can I modify the models selection by solver,(conjugate gradient is what I
would prefer) - by VBA commands?

please advise. many thanks again.

P.S. - I am very upset that I wasted the whole day trying to run the elegant
version. I like that one. It is clear and makes sense. Why it stops half-way??

 

[Dana DeLouis]

Just to add for a Solver Loop.
You will keep adding Constraints in the loop.
The easiest way is to just Reset:

For r = 1 to 10
SolverReset
SolverOk SetCell...etc


= = =
Dana DeLouis

 

[BEETAL]

Dear DANA,

My salutation. The suggestion was " A Master's brilliant insight".

I am grateful to Jon and Dana.

Thanks again.
Now some explanation why I was so desperate to use Jon's code. Jon's code
elegantly tells me "how to use the constraints(I have tested it on one
constraint only and hope it can be used for tens and more constraints)".
Jon's few lines are pretty elegant. Now the last request as of today(sorry
again to be bothering you)

If anyone of you can advise me as to how to write the following constraint
code to incorporate more than one address for more than one constraint, I
shall be grateful(which already I am).

sByChange = Range("myrangetrial").Offset(iRow - 52, -3).Address

Do I have to create as many sBychange as the constraints or do I have to
create another loop like,
dim sbychange( ) as array
For k = 1 to 5
sByChange(1,K) = Range("myrangetrial").Offset(iRow - 52, -3 + k).Address
next k
Something like the above three will do or not! Please advice.

Thank you so much.

Regards

Siddh.

 

[Dana DeLouis]

Hi. Are there any ideas here you can use?
Your description of the problem is not very clear to me.
If Column F is related to Column C. Is Column G related to D?
If not, then if F is zero because C is set to the correct value, what
happens when G is zero and a different value is in C? (F won't be zero
anymore!)
This small demo limits the changing cell in Column C to 0-100.

Sub Demo()
Dim R As Long
Dim Result As Long
For R = 3 To 20
SolverReset
SolverOk Cells(R, 6), 3, 0, Cells(R, 3)
SolverAdd Cells(R, 3), 3, 0 '>= 0
SolverAdd Cells(R, 3), 1, 100 '<= 100
Result = SolverSolve(True)
If Result >= 3 Then
'Error: Did not converge to a soluion
Debug.Print "No solution in Row: "; R
End If
Next R
End Sub


I would be guessing, but it sounds like your matrix, and your "Changing
Cells" need to be the same size, and square in size, to do a "conjugate
gradient" Six Columns, by 250 rows, sounds like you have too many
equations, and too few variables.

'= = =
'HTH :>)
Dana DeLouis