Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to RUN Solver mutiple times (on different target cells)
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. -- beetal |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to RUN Solver mutiple times (on different target cells)
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "BEETAL" wrote in message ... 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. -- beetal |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to RUN Solver mutiple times (on different target cell
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 "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "BEETAL" wrote in message ... 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. -- beetal |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to RUN Solver mutiple times (on different target cell
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. -- beetal "BEETAL" wrote: 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 "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "BEETAL" wrote in message ... 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. -- beetal |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to RUN Solver mutiple times (on different target cell
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "BEETAL" wrote in message ... 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. -- beetal "BEETAL" wrote: 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 "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "BEETAL" wrote in message ... 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. -- beetal |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to RUN Solver mutiple times (on different target cell
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?? -- beetal "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "BEETAL" wrote in message ... 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. -- beetal "BEETAL" wrote: 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 "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "BEETAL" wrote in message ... 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. -- beetal |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to RUN Solver mutiple times (on different target cell
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 wrote: 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?? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to RUN Solver mutiple times (on different target cell
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. -- beetal "Dana DeLouis" wrote: 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 ( brilliant insight) SolverOk SetCell...etc = = = Dana DeLouis BEETAL wrote: 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?? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to RUN Solver mutiple times (on different target cell
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 BEETAL wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Several target cells in the solver | Excel Discussion (Misc queries) | |||
deleting cells that are controlled using Target code | Excel Discussion (Misc queries) | |||
How do I set more than 1 target cell using MS Solver? | Excel Worksheet Functions | |||
target cells in solver | Excel Programming | |||
Goalseek / Solver Target Value | Excel Worksheet Functions |