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.

 

[Tushar Mehta]

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

 

[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

 

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

 

[Tushar Mehta]

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