Solver programming questions

[gvm]

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 spreadsheet, 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

 

[Per Jessen]

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]

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

 

[Dana DeLouis]

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]

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]

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]

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

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

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]

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

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

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

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

:

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

:

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"
<[email protected]> skrev i meddelelsen
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 spreadsheet, 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

 

[gvm]

Thanks Dana, everything is OK now, thanks very much ... Greg

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

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

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

:

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

:

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"
<[email protected]> skrev i meddelelsen
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 spreadsheet, 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