Need help writing Excel macro to solve a cell in each row of a spreadsheet

[Chris]

I am trying to write an Excel macro to run solver in order to iterate a
solution for a cell in each row in a spreadsheet. Each row contains the
target, changing, and contraint cells. However, I have over 10,000 rows
that need to be iterated. The problem I'm having is getting the macro to
move to the next row after one has been solved. Any help would be
greatly appreciated.



*** Sent via Developersdex http://www.developersdex.com ***

[Tushar Mehta]

In article <[email protected]>, [email protected]
says...
>
> I am trying to write an Excel macro to run solver in order to iterate a
> solution for a cell in each row in a spreadsheet. Each row contains the
> target, changing, and contraint cells. However, I have over 10,000 rows
> that need to be iterated. The problem I'm having is getting the macro to
> move to the next row after one has been solved. Any help would be
> greatly appreciated.
>
>
Why don't you share the code you have for one row and someone should be
able to generalize it.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

[Chris]

The code i have right now is the following, I'm also getting a "run-time
error '424': Object required"

Sub BetaSolver()

Do
Range("AT8").Select
ActiveCell.FormulaR1C1 = "1"
SolverReset
SolverOptions MaxTime:=100, Iterations:=100,
Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1,
_
IntTolerance:=1, Scaling:=False, Convergence:=0.000001,
AssumeNonNeg:=False
SolverOk SetCell:="$AX8", MaxMinVal:=2, ValueOf:="0", ByChange:="$AT8"
SolverAdd CellRef:="$AR8", Relation:=1, FormulaText:="1"
SolverAdd CellRef:="$AR8", Relation:=3, FormulaText:="-1"
SolverAdd CellRef:="$AS8", Relation:=1, FormulaText:="1"
SolverAdd CellRef:="$AS8", Relation:=3, FormulaText:="-1"
SolverAdd CellRef:="$AT8", Relation:=1, FormulaText:="1"
SolverAdd CellRef:="$AT8", Relation:=3, FormulaText:="-1"
SolverSolve UserFinish:=True
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(Active.Cell(0, -1))

End Sub


*** Sent via Developersdex http://www.developersdex.com ***

[Tushar Mehta]

OK, since you are "stepping down" column AT selecting one cell at a
time, your ActiveCell will always be in column AT starting with AT8.
So, you can change your SolverOK statement to be:

SolverOk SetCell:=ActiveCell.Offset(0, 4).Address, _
MaxMinVal:=2, ValueOf:="0", ByChange:=ActiveCell.Address

You can change the various SolverAdd statements similarly. Column AS
is at offset -1 relative to col. AT.

You don't indicate what line generates the "object required" error. My
guess would be it is from Active.Cell(...). Don't you mean ActiveCell?

--
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...
>
> The code i have right now is the following, I'm also getting a "run-time
> error '424': Object required"
>
> Sub BetaSolver()
>
> Do
> Range("AT8").Select
> ActiveCell.FormulaR1C1 = "1"
> SolverReset
> SolverOptions MaxTime:=100, Iterations:=100,
> Precision:=0.000001, AssumeLinear _
> :=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1,
> _
> IntTolerance:=1, Scaling:=False, Convergence:=0.000001,
> AssumeNonNeg:=False
> SolverOk SetCell:="$AX8", MaxMinVal:=2, ValueOf:="0", ByChange:="$AT8"
> SolverAdd CellRef:="$AR8", Relation:=1, FormulaText:="1"
> SolverAdd CellRef:="$AR8", Relation:=3, FormulaText:="-1"
> SolverAdd CellRef:="$AS8", Relation:=1, FormulaText:="1"
> SolverAdd CellRef:="$AS8", Relation:=3, FormulaText:="-1"
> SolverAdd CellRef:="$AT8", Relation:=1, FormulaText:="1"
> SolverAdd CellRef:="$AT8", Relation:=3, FormulaText:="-1"
> SolverSolve UserFinish:=True
> ActiveCell.Offset(1, 0).Select
> Loop Until IsEmpty(Active.Cell(0, -1))
>
> End Sub
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

[Chris]

The macro works perfectly. I had to move the "Do" command down one line
and you were right the "Cell.Set" was the line causing the run-time
error. Thank You for your help, it is greatly appreciated! One more
question, is it possible to stop or cancel the macro while its running?



*** Sent via Developersdex http://www.developersdex.com ***

[Tushar Mehta]

In article <#[email protected]>,
[email protected] says...
> The macro works perfectly. I had to move the "Do" command down one line
> and you were right the "Cell.Set" was the line causing the run-time
> error. Thank You for your help, it is greatly appreciated! One more
> question, is it possible to stop or cancel the macro while its running?
>
>

Yes, you can interrupt a macro as long as the developer has not
disabled that capability. Use ESC or CTRL+Break.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions