Macro Code Problem

[unique]

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

[msuryexcel]

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

[LCK]

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.

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>