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 *** |
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 |
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 *** |
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 *** |
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 |
All times are GMT +1. The time now is 02:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com