Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Code Problem
Hello,
I am trying to automate the 'solver' function so that we dont have to go through the menu to get the solver answer. The solver works and excel finds feasible solutions. But when attaching a macro, to this the following compiler error appears. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 28/10/2005 by IT Services ' ' SolverOk SetCell:="$E$9", MaxMinVal:=2, ValueOf:="0", ByChange:="$D$2:$D$7" SolverSolve End Sub with the 'SolverOk' code highlighted in blue. And then the Sub Macro1() code, highlighted in yellow. What the problem with this macro. regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Code Problem
Hello: I found this piece of code from this forum (item on 9/9/05 by Tushar Mehta) very useful. In your code the second sentence should be on the same line as the SolverOK You seem to have missed the "hiphen" at the end of first line msuryexcel ******** 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" SolverSolve UserFinish:=True ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(Active.Cell(0, -1)) End Sub -- msuryexcel ------------------------------------------------------------------------ msuryexcel's Profile: http://www.excelforum.com/member.php...o&userid=28161 View this thread: http://www.excelforum.com/showthread...hreadid=480134 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Code Problem
Hi,
It's been a while since I worked with Excel Solver.. I used to have the same problem. I think that you have to Reference to the Solver Library first: Tools References, and check the reference for Solver. I you have already done it, and the debugger still pausing in the solver code, add a line with at the beggining of your code: Solver.Reset Regards LCK PD. You can find more information about solver on http://www.frontsys.com. Those people developed Solver platform and licensed it to MS. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Code Problem
http://support.microsoft.com/default...b;en-us;843304
How to create Visual Basic macros by using Excel Solver in Excel 97 does say you need to create the reference as LCK mentions. If you want to do it in code: Sub SolverInstall() '// Dana DeLouis Dim wb As Workbook On Error Resume Next ' Set a Reference to the workbook that will hold Solver Set wb = ActiveWorkbook With wb.VBProject.References .Remove .Item("SOLVER") End With With AddIns("Solver Add-In") .Installed = False .Installed = True wb.VBProject.References.AddFromFile .FullName End With End Sub -- Regards, Tom Ogilvy "unique" wrote in message ... Hello, I am trying to automate the 'solver' function so that we dont have to go through the menu to get the solver answer. The solver works and excel finds feasible solutions. But when attaching a macro, to this the following compiler error appears. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 28/10/2005 by IT Services ' ' SolverOk SetCell:="$E$9", MaxMinVal:=2, ValueOf:="0", ByChange:="$D$2:$D$7" SolverSolve End Sub with the 'SolverOk' code highlighted in blue. And then the Sub Macro1() code, highlighted in yellow. What the problem with this macro. regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
read macro code by vb code | Excel Programming | |||
do anybody have a sample code for executing excel macro from vb code?<eom | Excel Programming | |||
VBA Code Problem | Excel Programming | |||
code problem | Excel Programming | |||
Application.run Macro Code Problem | Excel Programming |