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

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"
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

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"
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

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"
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

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"
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

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"
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