Re: Solver programming questions

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

> are there examples of coded constraints that you might refer me to?

Hi. Glad you got something working. :>)
Recording a macro usually works. I don't know why it isn't.
Below, the '1 and `3 can be figured out by their position in the drop-down list in Solver's constraint menu. (less than or equal is the first one for example)

SolverAdd CellRef:="A2", Relation:=3, FormulaText:="2" '>=2
SolverAdd CellRef:="A2", Relation:=1, FormulaText:="10" '<=10

' Or you can just add them like this if you wish...
SolverAdd "A2", 1, "10" ' A2 <= 10


Consider using
SolverReset
at the beginning of your code to erase any Solver data.
This allows you make sure you do not have any constraints that you are not aware of.

= = =
HTH :>)
Dana DeLouis



gvm wrote:
Thanks Dana, I omitted to put C1 in exclamation marks so that part works fine now.

Can you help me code constraints in the macro please? Creating a macro by recording the use of Solver did not pickup the constraint statements. For example are there examples of coded constraints that you might refer me to please? TIA .... Greg

"Dana DeLouis" wrote:

Hi. I don't see your code, but this simple example worked for me.

Sub Demo()
[C1] = 10
SolverOk _
SetCell:="A3", _
MaxMinVal:=3, _
ValueOf:=Range("C1").Value, _
ByChange:="A2"
SolverSolve True

[C1] = 30 'New Value in C1
SolverOk _
SetCell:="A3", _
MaxMinVal:=3, _
ValueOf:=Range("C1").Value, _
ByChange:="A2"
SolverSolve True
End Sub

= = =
HTH :>)
Dana DeLouis


gvm wrote:
Thanks Dana for pointing out that I was specifying text. Unfortunately though, I get the following message when using both your suggestions...
Runtime error 1004 Method ‘Range’ of object ‘_Global’ failed
Can you see what I am doing incorrectly? TIA ... Greg

"Dana DeLouis" wrote:

ValueOf:="Range($C$1)",

Hi. You are setting it to a string "Rang....)"

What you want to just the range reference:
ValueOf:= Range($C$1)

However, may take care of it, but it might be slightly better to just set it to the "value", and not the cell itself.
Perhaps:

ValueOf:=Range(C1).Value

= = =
HTH
Dana DeLouis


gvm wrote:
Thanks Per, it is helpful. The macro doesn't work yet unfortunately. This is how I implemented your syntax advice ... ValueOf:="Range($C$1)". Does that look right? I also entered the SolverReset command at the start of the macro. The macro compiles OK and I have stepped through the macro and each command appears to execute, but the solver does not pickup the value of c1 as the target then run. I think the problem is in the Solverok instruction. Here's the full macro...
Sub Macro2()
SolverReset
SolverOk SetCell:="$A$3", MaxMinVal:=3, ValueOf:="Range($C$1)", ByChange:="$A$2"
SolverSolve
End Sub

"Per Jessen" wrote:

Hi

The syntax for a cell reference in VBA is :

Range($A$3")

I have never used the solver function, but I found this in the VBA help:

SolverSolve Function Example

This example uses the Solver functions to maximize gross profit in a business problem. The SolverSolve function begins the Solver solution run.

Worksheets("Sheet1").Activate
SolverReset
SolverOptions Precision:=0.001
SolverOK SetCell:=Range("TotalProfit"), _
MaxMinVal:=1, _
ByChange:=Range("C4:E6")
SolverAdd CellRef:=Range("F4:F6"), _
Relation:=1, _
FormulaText:=100
SolverAdd CellRef:=Range("C4:E6"), _
Relation:=3, _
FormulaText:=0
SolverAdd CellRef:=Range("C4:E6"), _
Relation:=4
SolverSolve UserFinish:=False
SolverSave SaveArea:=Range("A33")Hopes this helps---Per"gvm" <gvm@xxxxxxxxxxxxxxxxxxxxxxxxx> skrev i meddelelsen news:E478C47E-353E-47B6-B9A5-9890FB0248D8@xxxxxxxxxxxxxxxx
I created a macro that uses Solver by recording one, selecting solver as a
VBA reference, and changing the target value from a number to a cell
reference. In the spread***, cell A3 = A1*A2. Cells A1 and A2 simply
contain values. C1 contains the number that Solver should use as the target
value. Here's the macro code…

Sub Macro2()
'
' Macro2 Macro
SolverOk SetCell:="$A$3", MaxMinVal:=3, ValueOf:="C1", ByChange:="$A$2"
SolverSolve
End Sub

First problem is that the solver runs successfully on the first occasion the
macro is run, but running the macro after changing the number in C1 creates
erroneous output. The reason seems to be that Solver contains target number
from the first occasion. I think this because when I open the Solver dialogue
box, it contains references from the first occasion. Is there a need for a
line of code that somehow resets Solver?

Second problem is I would like to modify the code to automatically accept
the output Solver finds, regardless of whether it succeeded in finding a
solution or not. How do I change the code to do that please?

Finally, the Solver constraints did not appear in the macro that was
recorded. Where can I find information about coding constraints into the
macro please? TIA …. Greg

.

Quantcast