solver in macro

[Guest]

Hi Everybody,

I would like to create a macro for using the solver, but i can't. I would
like if the ValueOf could come from a cell, for example the "A1".

That's how it looks now, but I get compile error message:

Sub solver()
'

SolverOk SetCell:="$D$24", MaxMinVal:=3, ValueOf:="600000", ByChange:= _
"$C$8:$C$22"
SolverSolve
End Sub


Thank you in adnvance for your help!

 

[Rob Bovey]

I would like to create a macro for using the solver, but i can't. I would
like if the ValueOf could come from a cell, for example the "A1".

That's how it looks now, but I get compile error message:

Sub solver()
'

SolverOk SetCell:="$D$24", MaxMinVal:=3, ValueOf:="600000", ByChange:=
_
"$C$8:$C$22"
SolverSolve
End Sub

First make sure that Solver is open. This may require selecting Solver
from the Excel menu and then dismissing the dialog. You'll know Solver is
open when you can see the entry SOLVER (SOLVER.XLA) in the Visual Basic
Editor Project window.

Next, select your workbook's project in the Visual Basic Editor Project
window and choose Tools/References from the menu. Put a check mark beside
the SOLVER reference and click OK. Save your workbook. Your code should work
now.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Dana DeLouis]

Hi. Make sure Solver is loaded as an Add-Inn from the worksheet menu, then
in the vba window...

XL2000: Compile Error Occurs Running a Recorded Macro That Uses the Solver
Add-in
http://support.microsoft.com/default.aspx?scid=kb;en-us;213689

As a side note, if your changing cells are small in value (I'm looking at
target of 600,000), and you are having a problem with a solution, you may
want to try the following option:
SolverOptions Scaling:=True

HTH

 

[Guest]

Thank you very much for your quick help, but unfortunatelly, I still can't
use it. Maybe i am too unclever.

Thanks anyway,

my regards

 

[Guest]

Hello, thank you for your help, but i still can't use it:-( Probably I do
something wrong.

thanks and bye

 

[Tushar Mehta]

I'm assuming you solved your initial compile error. What problem do
you have now?

Also, to use a cell value in place of the 600000 use the code below.
Together with setting a reference to the Solver add-in, it works just
fine.

SolverOk SetCell:="$D$24", MaxMinVal:=3, _
ValueOf:=Range("a1").Value, _
ByChange:="$C$8:$C$22"
SolverSolve

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions