Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write an Excel Macro to run Solver repeatedly?
I am trying to run solver 36 times on a worksheet as I have an iterative
process that I need to solve. When I run it on individual cells I get a proper answer returned but when I try to create a macro that runs solver on all 36 cells none of the cells seem to update. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write an Excel Macro to run Solver repeatedly?
Supply details of excatly what you want to do. Running solver through a loop is not a big deal. -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=399305 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write an Excel Macro to run Solver repeatedly?
I have 36 cells (6 rows and 6 columns) which have a multivariable equation
and so I need to run solver on each of these cells. I'm not a VBA programmer by any means so I recorded a macro of me running solver on one cell, then just copied that 35 times in Word, ran find/replace to change the cell references, then copied it back into VBA. I created the reference to solver to get rid of the undefined sub error. Now, when I run the macro, it's clear that solver "runs," but for whatever reason it doesn't change the values. Here's the code for a particular cell- SolverReset SolverOk SetCell:="$i$14", MaxMinVal:=2, ByChange:="$i$15" SolverChange CellRef:="$i$14", Relation:=3, FormulaText:="$i$13" SolverSolve Now imagine that 35 more times with the cell references different, and that's what I am trying to do. Any thoughts? Thanks for your assistance- "davidm" wrote: Supply details of excatly what you want to do. Running solver through a loop is not a big deal. -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=399305 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write an Excel Macro to run Solver repeatedly?
Sorry- I did not mean to imply I had a 6x6 matrix but that's definitely what
I wrote. To answer your questions: The target cells are I14-N14, I18-N18, I22-N22, I26-N26, I30-N30, I34-N34 The by changing cells are I15-N15, I19-N19, I23-N23, I27-N27, I31-N31, I35-N35 The constraints are I14=I13, etc As I noted, when I run solver manually with the problem formulated as above, it finds a solution; however when I run a macro that calls solver with the exact same formulation, it fails. Thanks again for your help- "Tushar Mehta" wrote: The last post has some holes in it. You first indicate you have a 6 row by 6 column matrix and want to optimize each cell independent of the others. But, then in the code you share, I14, I15, and I13 are the cells involved. So, if I15 is a 'by changing' cell, how can it possibly be a candidate for a future optimization? So, where are the 36 cells you want to optimize? For *each* of the 36 cells, which is/are the 'by changing' cell(s) and what is/are the constraint(s)? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have 36 cells (6 rows and 6 columns) which have a multivariable equation and so I need to run solver on each of these cells. I'm not a VBA programmer by any means so I recorded a macro of me running solver on one cell, then just copied that 35 times in Word, ran find/replace to change the cell references, then copied it back into VBA. I created the reference to solver to get rid of the undefined sub error. Now, when I run the macro, it's clear that solver "runs," but for whatever reason it doesn't change the values. Here's the code for a particular cell- SolverReset SolverOk SetCell:="$i$14", MaxMinVal:=2, ByChange:="$i$15" SolverChange CellRef:="$i$14", Relation:=3, FormulaText:="$i$13" SolverSolve Now imagine that 35 more times with the cell references different, and that's what I am trying to do. Any thoughts? Thanks for your assistance- "davidm" wrote: Supply details of excatly what you want to do. Running solver through a loop is not a big deal. -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=399305 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write an Excel Macro to run Solver repeatedly?
The foll. tested code used a linear model. If your model is not
linear, ensure AssumeLinear:=False. Note that for a non-linear model, Solver may or may not find a solution given the current starting values. Option Explicit Sub MultColumnMultiRowSolver() Dim RowIdx As Variant, ColIdx As Byte, TargCell As Range For ColIdx = Columns("I").Column To Columns("N").Column For Each RowIdx In Array(14, 18, 22, 26, 30, 34) SolverReset SolverOptions MaxTime:=100, Iterations:=100, _ Precision:=0.000001, AssumeLinear:=True, _ StepThru:=False, Estimates:=1, _ Derivatives:=1, SearchOption:=1, _ IntTolerance:=5, Scaling:=False, _ Convergence:=0.0001, AssumeNonNeg:=False Set TargCell = ActiveSheet.Cells(RowIdx, ColIdx) SolverAdd CellRef:=TargCell, _ Relation:=3, _ FormulaText:=TargCell.Offset(-1, 0) SolverOk SetCell:=TargCell, _ MaxMinVal:=2, ValueOf:="0", _ ByChange:=TargCell.Offset(1, 0) SolverSolve UserFinish:=True Next RowIdx Next ColIdx End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Sorry- I did not mean to imply I had a 6x6 matrix but that's definitely what I wrote. To answer your questions: The target cells are I14-N14, I18-N18, I22-N22, I26-N26, I30-N30, I34-N34 The by changing cells are I15-N15, I19-N19, I23-N23, I27-N27, I31-N31, I35-N35 The constraints are I14=I13, etc As I noted, when I run solver manually with the problem formulated as above, it finds a solution; however when I run a macro that calls solver with the exact same formulation, it fails. Thanks again for your help- "Tushar Mehta" wrote: The last post has some holes in it. You first indicate you have a 6 row by 6 column matrix and want to optimize each cell independent of the others. But, then in the code you share, I14, I15, and I13 are the cells involved. So, if I15 is a 'by changing' cell, how can it possibly be a candidate for a future optimization? So, where are the 36 cells you want to optimize? For *each* of the 36 cells, which is/are the 'by changing' cell(s) and what is/are the constraint(s)? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have 36 cells (6 rows and 6 columns) which have a multivariable equation and so I need to run solver on each of these cells. I'm not a VBA programmer by any means so I recorded a macro of me running solver on one cell, then just copied that 35 times in Word, ran find/replace to change the cell references, then copied it back into VBA. I created the reference to solver to get rid of the undefined sub error. Now, when I run the macro, it's clear that solver "runs," but for whatever reason it doesn't change the values. Here's the code for a particular cell- SolverReset SolverOk SetCell:="$i$14", MaxMinVal:=2, ByChange:="$i$15" SolverChange CellRef:="$i$14", Relation:=3, FormulaText:="$i$13" SolverSolve Now imagine that 35 more times with the cell references different, and that's what I am trying to do. Any thoughts? Thanks for your assistance- "davidm" wrote: Supply details of excatly what you want to do. Running solver through a loop is not a big deal. -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=399305 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write an Excel Macro to run Solver repeatedly?
Tushar-
Thank you so much for your help. I really appreciate all the time you have put into helping me out. However, when I ran this macro, it ended up printing the text $I$14 in cell I14, $J$14 in J14, etc for all 36 cells. I don't know what is going on but something is not working properly. Is it possible to get solver to function properly in a macro in Excel 2000? "Tushar Mehta" wrote: The foll. tested code used a linear model. If your model is not linear, ensure AssumeLinear:=False. Note that for a non-linear model, Solver may or may not find a solution given the current starting values. Option Explicit Sub MultColumnMultiRowSolver() Dim RowIdx As Variant, ColIdx As Byte, TargCell As Range For ColIdx = Columns("I").Column To Columns("N").Column For Each RowIdx In Array(14, 18, 22, 26, 30, 34) SolverReset SolverOptions MaxTime:=100, Iterations:=100, _ Precision:=0.000001, AssumeLinear:=True, _ StepThru:=False, Estimates:=1, _ Derivatives:=1, SearchOption:=1, _ IntTolerance:=5, Scaling:=False, _ Convergence:=0.0001, AssumeNonNeg:=False Set TargCell = ActiveSheet.Cells(RowIdx, ColIdx) SolverAdd CellRef:=TargCell, _ Relation:=3, _ FormulaText:=TargCell.Offset(-1, 0) SolverOk SetCell:=TargCell, _ MaxMinVal:=2, ValueOf:="0", _ ByChange:=TargCell.Offset(1, 0) SolverSolve UserFinish:=True Next RowIdx Next ColIdx End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Sorry- I did not mean to imply I had a 6x6 matrix but that's definitely what I wrote. To answer your questions: The target cells are I14-N14, I18-N18, I22-N22, I26-N26, I30-N30, I34-N34 The by changing cells are I15-N15, I19-N19, I23-N23, I27-N27, I31-N31, I35-N35 The constraints are I14=I13, etc As I noted, when I run solver manually with the problem formulated as above, it finds a solution; however when I run a macro that calls solver with the exact same formulation, it fails. Thanks again for your help- "Tushar Mehta" wrote: The last post has some holes in it. You first indicate you have a 6 row by 6 column matrix and want to optimize each cell independent of the others. But, then in the code you share, I14, I15, and I13 are the cells involved. So, if I15 is a 'by changing' cell, how can it possibly be a candidate for a future optimization? So, where are the 36 cells you want to optimize? For *each* of the 36 cells, which is/are the 'by changing' cell(s) and what is/are the constraint(s)? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have 36 cells (6 rows and 6 columns) which have a multivariable equation and so I need to run solver on each of these cells. I'm not a VBA programmer by any means so I recorded a macro of me running solver on one cell, then just copied that 35 times in Word, ran find/replace to change the cell references, then copied it back into VBA. I created the reference to solver to get rid of the undefined sub error. Now, when I run the macro, it's clear that solver "runs," but for whatever reason it doesn't change the values. Here's the code for a particular cell- SolverReset SolverOk SetCell:="$i$14", MaxMinVal:=2, ByChange:="$i$15" SolverChange CellRef:="$i$14", Relation:=3, FormulaText:="$i$13" SolverSolve Now imagine that 35 more times with the cell references different, and that's what I am trying to do. Any thoughts? Thanks for your assistance- "davidm" wrote: Supply details of excatly what you want to do. Running solver through a loop is not a big deal. -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=399305 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write an Excel Macro to run Solver repeatedly?
While obviously people are willing to help those who need help, it
would really expedite the process if you'd share all relevant information up front. Your description of the problem may make perfect sense to you, but just look at this discussion and how seemingly reluctant you have appeared at sharing the problem. This bit about XL2000 is in your *4th* post on the subject. In any case, change the cell references to cell addresses as in SolverAdd CellRef:=TargCell.Address( _ RowAbsolute:=True, ColumnAbsolute:=True), _ Relation:=3, _ FormulaText:=TargCell.Offset(-1, 0) _ .Address(RowAbsolute:=True, ColumnAbsolute:=True) SolverOk SetCell:=TargCell.Address( _ RowAbsolute:=True, ColumnAbsolute:=True), _ MaxMinVal:=2, ValueOf:="0", _ ByChange:=TargCell.Offset(1, 0) _ .Address(RowAbsolute:=True, ColumnAbsolute:=True) -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Tushar- Thank you so much for your help. I really appreciate all the time you have put into helping me out. However, when I ran this macro, it ended up printing the text $I$14 in cell I14, $J$14 in J14, etc for all 36 cells. I don't know what is going on but something is not working properly. Is it possible to get solver to function properly in a macro in Excel 2000? {snip} |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to repeatedly find duplicate data | Excel Discussion (Misc queries) | |||
Macro - Copy and Paste repeatedly skipping cells | Excel Discussion (Misc queries) | |||
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? | Excel Worksheet Functions | |||
excel solver macro | Excel Programming | |||
macro using excel solver | Excel Programming |