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.

Supply details of excatly what you want to do. Running solver through a loop is not a big deal.

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

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 <[email protected]>,
[email protected] 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
> >
> >
>

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 <[email protected]>,
> [email protected] 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
> > >
> > >
> >
>

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 <[email protected]>,
[email protected] 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 <[email protected]>,
> > [email protected] 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
> > > >
> > > >
> > >
> >
>

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 <[email protected]>,
> [email protected] 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 <[email protected]>,
> > > [email protected] 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
> > > > >
> > > > >
> > > >
> > >
> >
>

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 <[email protected]>,
[email protected] 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}