Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help writing Excel macro to solve a cell in each row of a spreadsheet
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help writing Excel macro to solve a cell in each row of a spreadsheet
In article ,
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help writing Excel macro to solve a cell in each row of a spreadsheet
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help writing Excel macro to solve a cell in each row of a spreadsheet
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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help writing Excel macro to solve a cell in each row of a spreadsheet
In article ,
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Writing a macro that will exit a spreadsheet | New Users to Excel | |||
When writing a macro in excel workbook, how do I refer to ea cell | Excel Programming | |||
Writing bitmap to Excel Spreadsheet | Excel Programming | |||
Writing to a excel spreadsheet is slow. | Excel Programming | |||
Writing a macro so that when saving a spreadsheet the data cannot be changed | Excel Programming |