VBA Excel Solver ValueOf: Reference Problem

[Karl]

Hi,

I have a problem regarding the VBA Solver Code. The Code looks like
this:

SolverOk SetCell:=[DCRAve], MaxMinVal:=3, ValueOf:=[MO_DCRMin].Value,
ByChange:=[MO1DCR]
SolverSolve

The Solver works fine if I put a Value (ex. 0.4) in ValueOf but does
not work with a reference to a Spreadsheet Cell (neither
Range("B12").Value, Range("B12"), nor [MO_DCRMin]

Has anybody an idea how to solve this problem??

Also, I do not want the Solver Dialogbox to pop up and ask me to accept
the stuff. It shall be accept in all of the cases.

How to do that??

Thank You very much for helping me out,

Karl

 

[Dana DeLouis]

Not sure, but the "ValueOf" needs to be a number, and not a "reference" to a
cell. Usually, using ".value" works.
As such, both of your examples below worked ok for me.

ValueOf:=[MO_DCRMin].Value
ValueOf:=Range("B12").Value

Also, I do not want the Solver Dialogbox to pop up and ask...

SolverSolve True

HTH
--
Dana DeLouis
Win XP & Office 2003

Hi,

I have a problem regarding the VBA Solver Code. The Code looks like
this:

SolverOk SetCell:=[DCRAve], MaxMinVal:=3, ValueOf:=[MO_DCRMin].Value,
ByChange:=[MO1DCR]
SolverSolve

The Solver works fine if I put a Value (ex. 0.4) in ValueOf but does
not work with a reference to a Spreadsheet Cell (neither
Range("B12").Value, Range("B12"), nor [MO_DCRMin]

Has anybody an idea how to solve this problem??

Also, I do not want the Solver Dialogbox to pop up and ask me to accept
the stuff. It shall be accept in all of the cases.

How to do that??

Thank You very much for helping me out,

Karl

 

[Karl]

Hei Dana,

thanks very much, I still do not know why it does not work. But I got
around the problem by adding a constraint for DCRAve to be the same
Value as DCRMin. The Solver usually gives a reply that he found an
unsatisfied solution which works, and with your help I could get rid of
the MSGBox (SolverSolve True).

Thank You,

Karl

 

[Dana DeLouis]

Hi. Just off the top of my head, it sure doesn't sound like your new
constraint is a good idea. That constraint is taken care of in Solver's
"SolverOk ..." line.
Just throwing this out. Using ValueOf:=Range("B12").Value should work. Is
it pointing to the correct cell reference?
For your range names, how about doing this on a worksheet...Insert | Name |
Paste... | Paste List.
Then, make sure all your range names are spelled correctly, and all
reference the "Active" and "Correct" worksheet.
(Ie...In :=[MO_DCRMin]., make sure "O" is the correct form... Letter O or
Number 0. ) (I've never done that...:>)
HTH