| 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 |
| 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 | |||
Hybrid Mode
