Macros involving SOLVER... function

[Pixies]

My intial problem was to solve a simultaneous equation using excel.

You can do this using Tools > Solver...

Now I wanted to create a macro so everytime I change my constraints< I can run the macro which would in turn run the commands in Solver...

I did this BUT when I actually ran the macro again I got this error:

Compile Error:
Sub or Function not defined.

Macro:
Sub last()
'
' last Macro
' Macro recorded 19.04.2006 by Lewis Holland
'

'
SolverOk MaxMinVal:=1, ValueOf:="0", ByChange:="$D$9:$D$10"
SolverAdd CellRef:="$A$9:$A$10", Relation:=2, FormulaText:="$B$9:$B$10"
SolverOk MaxMinVal:=1, ValueOf:="0", ByChange:="$D$9:$D$10"
SolverSolve
End Sub

Can anyone help solve this?
Can you record a macro which actually uses this Solver.. function?

[Dana DeLouis]

In the vba editor, go to Tools | Reference, and set a reference to "Solver."
In your code, I believe your left out a reference to the Target Cell (ie
SetCell).
If you keep calling Solver, you will eventually have too many redundant
constraints.
I find it best to start from scratch with SolverReset.

SolverReset
SolverOk SetCell:="A1", MaxMinVal:=1, ByChange:="D9:D10"
SolverAdd CellRef:="A9:A10", Relation:=2, FormulaText:="B9:B10"
SolverSolve True

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Pixies" <[email protected]> wrote in
message news:[email protected]...
>
> My intial problem was to solve a simultaneous equation using excel.
>
> You can do this using Tools > Solver...
>
> Now I wanted to create a macro so everytime I change my constraints< I
> can run the macro which would in turn run the commands in Solver...
>
> I did this BUT when I actually ran the macro again I got this error:
>
> Compile Error:
> Sub or Function not defined.
>
> Macro:
> Sub last()
> '
> ' last Macro
> ' Macro recorded 19.04.2006 by Lewis Holland
> '
>
> '
> SolverOk MaxMinVal:=1, ValueOf:="0", ByChange:="$D$9:$D$10"
> SolverAdd CellRef:="$A$9:$A$10", Relation:=2,
> FormulaText:="$B$9:$B$10"
> SolverOk MaxMinVal:=1, ValueOf:="0", ByChange:="$D$9:$D$10"
> SolverSolve
> End Sub
>
> Can anyone help solve this?
> Can you record a macro which actually uses this Solver.. function?
>
>
> --
> Pixies
> ------------------------------------------------------------------------
> Pixies's Profile:
> http://www.excelforum.com/member.php...o&userid=33642
> View this thread: http://www.excelforum.com/showthread...hreadid=534142
>

[Pixies]

Ok I added the reference & tried your other suggestions too.

The Macro seems to be running, but I don't get a solution.

Better explanation of my problem:
A B D
8 Eqn Const. Solution
9 0 62%
10 0 38%

A/B/D Are columns, 8,9,10 the rows.
A9: Formula A (Depending on d9 & 10)
A10: Formula B (Depending on d9 & 10)

Formula A = B9
Formula B = B10

As far as I know I had no reason to enter anything into the SetCell Section. I edited your suggestion to the following but still no sucess:

SolverReset
SolverOk SetCell:="", MaxMinVal:=1, ValueOf:="0", ByChange:="D9:D10"
SolverAdd CellRef:="A9:A10", Relation:=2, FormulaText:="B9:B10"
SolverSolve True
End Sub

[Dana DeLouis]

> As far as I know I had no reason to enter anything into the SetCell
> Section.

I don't think Solver knows by default which cell you are trying to
maximize.
As a side note, when Maximizing, Solver ignores the ValueOf:=0.

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Pixies" <[email protected]> wrote in
message news:[email protected]...
>
> Ok I added the reference & tried your other suggestions too.
>
> The Macro seems to be running, but I don't get a solution.
>
> Better explanation of my problem:
> A B D
> 8 Eqn Const. Solution
> 9 0 62%
> 10 0 38%
>
> A/B/D Are columns, 8,9,10 the rows.
> A9: Formula A (Depending on d9 & 10)
> A10: Formula B (Depending on d9 & 10)
>
> Formula A = B9
> Formula B = B10
>
> As far as I know I had no reason to enter anything into the SetCell
> Section. I edited your suggestion to the following but still no
> sucess:
>
> SolverReset
> SolverOk SetCell:="", MaxMinVal:=1, ValueOf:="0",
> ByChange:="D9:D10"
> SolverAdd CellRef:="A9:A10", Relation:=2, FormulaText:="B9:B10"
> SolverSolve True
> End Sub
>
>
> --
> Pixies
> ------------------------------------------------------------------------
> Pixies's Profile:
> http://www.excelforum.com/member.php...o&userid=33642
> View this thread: http://www.excelforum.com/showthread...hreadid=534142
>

[Pixies]

Ok.
I tried doing it exactly as you said.

But I didn't get any output.
Any reason for that?

(For extra info)
When I do the same process manually:
I am ignoring top section.
I get an output in D9:D10, which is correct for the 2 simultaneous equations I have got.

Excuse my ignorance with Macro's - it's my first time. Althought I have done other programming etc.

[Pixies]

Can you help me with the problem?