ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Macro for user defined Solver changing cells. (https://www.excelbanter.com/excel-programming/402935-macro-user-defined-solver-changing-cells.html)

[email protected]
Macro for user defined Solver changing cells.
 
Can anyone please help me with this...
The code for a "User defined Solver" is not working :( and me being
new in VBA can not work out why...Many thanks

Private Sub CommandButton7_Click()
Dim Cell1 As Range
Dim Cell2 As Range
Dim Cell3 As Range
Dim Cell4 As Range
Dim Cell5 As Range
Dim Cell6 As Range
Dim Cell7 As Range
Dim Cell8 As Range
With frmchecklist
..Show
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")
ElseIf .CheckBox3.Value = True Then
Set Cell3 = Range("Clinic")
ElseIf .CheckBox4.Value = True Then
Set Cell4 = Range("Admis")
ElseIf .CheckBox5.Value = True Then
Set Cell5 = Range("Other")
ElseIf .CheckBox6.Value = True Then
Set Cell6 = Range("MinorBasal")
ElseIf .CheckBox7.Value = True Then
Set Cell7 = Range("MajorBasal")
ElseIf .CheckBox8.Value = True Then
Set Cell8 = Range("PriceBasal")
End If
SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End With
End Sub

Jon Peltier
Macro for user defined Solver changing cells.
 
Try changing the SolverOK command:

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8).Address


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
...
Can anyone please help me with this...
The code for a "User defined Solver" is not working :( and me being
new in VBA can not work out why...Many thanks

Private Sub CommandButton7_Click()
Dim Cell1 As Range
Dim Cell2 As Range
Dim Cell3 As Range
Dim Cell4 As Range
Dim Cell5 As Range
Dim Cell6 As Range
Dim Cell7 As Range
Dim Cell8 As Range
With frmchecklist
.Show
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")
ElseIf .CheckBox3.Value = True Then
Set Cell3 = Range("Clinic")
ElseIf .CheckBox4.Value = True Then
Set Cell4 = Range("Admis")
ElseIf .CheckBox5.Value = True Then
Set Cell5 = Range("Other")
ElseIf .CheckBox6.Value = True Then
Set Cell6 = Range("MinorBasal")
ElseIf .CheckBox7.Value = True Then
Set Cell7 = Range("MajorBasal")
ElseIf .CheckBox8.Value = True Then
Set Cell8 = Range("PriceBasal")
End If
SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End With
End Sub



[email protected]
Macro for user defined Solver changing cells.
 
On 18 Dec, 20:21, "Jon Peltier"
wrote:
Try changing the SolverOK command:

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8).Address

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...



Can anyone please help me with this...
The code for a "User defined Solver" is not working :( and me being
new in VBA can not work out why...Many thanks

Private Sub CommandButton7_Click()
Dim Cell1 As Range
Dim Cell2 As Range
Dim Cell3 As Range
Dim Cell4 As Range
Dim Cell5 As Range
Dim Cell6 As Range
Dim Cell7 As Range
Dim Cell8 As Range
With frmchecklist
.Show
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")
ElseIf .CheckBox3.Value = True Then
Set Cell3 = Range("Clinic")
ElseIf .CheckBox4.Value = True Then
Set Cell4 = Range("Admis")
ElseIf .CheckBox5.Value = True Then
Set Cell5 = Range("Other")
ElseIf .CheckBox6.Value = True Then
Set Cell6 = Range("MinorBasal")
ElseIf .CheckBox7.Value = True Then
Set Cell7 = Range("MajorBasal")
ElseIf .CheckBox8.Value = True Then
Set Cell8 = Range("PriceBasal")
End If
SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End With
End Sub- Hide quoted text -

- Show quoted text -

Thank you Jon for your time and suggestion. I tried this but it does
not work. A message "Run time error 5, invalid procedure call or
argument" comes up. Any other ideas? Many thanks.

Jon Peltier
Macro for user defined Solver changing cells.
 
The arguments of the Solver functions have to be strings, so Address was one
correct thing to do.

Did you set a reference to Solver? In the VB editor, go to Tools menu
References, find Solver in the list, and check the textbox in front of it.

Have you initialized Solver? Run "SolverReset" before any other Solver VBA
commands.

Here's more about automating Solver:

http://peltiertech.com/Excel/SolverVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
...
On 18 Dec, 20:21, "Jon Peltier"
wrote:
Try changing the SolverOK command:

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8).Address

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...



Can anyone please help me with this...
The code for a "User defined Solver" is not working :( and me being
new in VBA can not work out why...Many thanks

Private Sub CommandButton7_Click()
Dim Cell1 As Range
Dim Cell2 As Range
Dim Cell3 As Range
Dim Cell4 As Range
Dim Cell5 As Range
Dim Cell6 As Range
Dim Cell7 As Range
Dim Cell8 As Range
With frmchecklist
.Show
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")
ElseIf .CheckBox3.Value = True Then
Set Cell3 = Range("Clinic")
ElseIf .CheckBox4.Value = True Then
Set Cell4 = Range("Admis")
ElseIf .CheckBox5.Value = True Then
Set Cell5 = Range("Other")
ElseIf .CheckBox6.Value = True Then
Set Cell6 = Range("MinorBasal")
ElseIf .CheckBox7.Value = True Then
Set Cell7 = Range("MajorBasal")
ElseIf .CheckBox8.Value = True Then
Set Cell8 = Range("PriceBasal")
End If
SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End With
End Sub- Hide quoted text -

- Show quoted text -

Thank you Jon for your time and suggestion. I tried this but it does
not work. A message "Run time error 5, invalid procedure call or
argument" comes up. Any other ideas? Many thanks.



Dana DeLouis
Macro for user defined Solver changing cells.
 
A message "Run time error 5, invalid procedure call or
argument" comes up.

If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")

Hi. In your code segment above, only 1 cell is getting set. The other
cells are not set.
Your Solver code is trying to set the Changing Cells to all of them via
Union.
Is this the desired results?
Basically, the Union of a valid range, and "Nothing" is really nothing in
Excel, and therefore this is being passed to Solver.

If you mean to only pass 1 Valid Changing cell to Solver, perhaps an idea
like the following might help.

Sub Demo()
Dim ChgCell As String
'etc ...your code...

If .CheckBox1.Value Then
Set ChgCell = Range("Cons")
ElseIf .CheckBox2.Value Then
Set ChgCell = Range("Nurse")
ElseIf .CheckBox3.Value Then
Set ChgCell = Range("Clinic")
'etc

SolverOK SetCell:=....., ByChange:=ChgCell.Address
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007


wrote in message
...
On 18 Dec, 20:21, "Jon Peltier"
wrote:
Try changing the SolverOK command:

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8).Address

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...



Can anyone please help me with this...
The code for a "User defined Solver" is not working :( and me being
new in VBA can not work out why...Many thanks

Private Sub CommandButton7_Click()
Dim Cell1 As Range
Dim Cell2 As Range
Dim Cell3 As Range
Dim Cell4 As Range
Dim Cell5 As Range
Dim Cell6 As Range
Dim Cell7 As Range
Dim Cell8 As Range
With frmchecklist
.Show
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")
ElseIf .CheckBox3.Value = True Then
Set Cell3 = Range("Clinic")
ElseIf .CheckBox4.Value = True Then
Set Cell4 = Range("Admis")
ElseIf .CheckBox5.Value = True Then
Set Cell5 = Range("Other")
ElseIf .CheckBox6.Value = True Then
Set Cell6 = Range("MinorBasal")
ElseIf .CheckBox7.Value = True Then
Set Cell7 = Range("MajorBasal")
ElseIf .CheckBox8.Value = True Then
Set Cell8 = Range("PriceBasal")
End If
SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End With
End Sub- Hide quoted text -

- Show quoted text -

Thank you Jon for your time and suggestion. I tried this but it does
not work. A message "Run time error 5, invalid procedure call or
argument" comes up. Any other ideas? Many thanks.



[email protected]
Macro for user defined Solver changing cells.
 
On 19 Dec, 16:04, "Dana DeLouis" wrote:
A message "Run time error 5, invalid procedure call or
argument" comes up.
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")

Hi. In your code segment above, only 1 cell is getting set. The other
cells are not set.
Your Solver code is trying to set the Changing Cells to all of them via
Union.
Is this the desired results?
Basically, the Union of a valid range, and "Nothing" is really nothing in
Excel, and therefore this is being passed to Solver.

If you mean to only pass 1 Valid Changing cell to Solver, perhaps an idea
like the following might help.

Sub Demo()
Dim ChgCell As String
'etc ...your code...

If .CheckBox1.Value Then
Set ChgCell = Range("Cons")
ElseIf .CheckBox2.Value Then
Set ChgCell = Range("Nurse")
ElseIf .CheckBox3.Value Then
Set ChgCell = Range("Clinic")
'etc

SolverOK SetCell:=....., ByChange:=ChgCell.Address
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007

wrote in message

...



On 18 Dec, 20:21, "Jon Peltier"
wrote:
Try changing the SolverOK command:

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8).Address

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

Can anyone please help me with this...
The code for a "User defined Solver" is not working :( and me being
new in VBA can not work out why...Many thanks

Private Sub CommandButton7_Click()
Dim Cell1 As Range
Dim Cell2 As Range
Dim Cell3 As Range
Dim Cell4 As Range
Dim Cell5 As Range
Dim Cell6 As Range
Dim Cell7 As Range
Dim Cell8 As Range
With frmchecklist
.Show
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")
ElseIf .CheckBox3.Value = True Then
Set Cell3 = Range("Clinic")
ElseIf .CheckBox4.Value = True Then
Set Cell4 = Range("Admis")
ElseIf .CheckBox5.Value = True Then
Set Cell5 = Range("Other")
ElseIf .CheckBox6.Value = True Then
Set Cell6 = Range("MinorBasal")
ElseIf .CheckBox7.Value = True Then
Set Cell7 = Range("MajorBasal")
ElseIf .CheckBox8.Value = True Then
Set Cell8 = Range("PriceBasal")
End If
SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End With
End Sub- Hide quoted text -

- Show quoted text -

Thank you Jon for your time and suggestion. I tried this but it does
not work. A message "Run time error 5, invalid procedure call or
argument" comes up. Any other ideas? Many thanks.- Hide quoted text -

- Show quoted text -

Thank you for your reply. The idea is that the user can choose the
cells for which the solver will find a solution by clicking on
different checkboxes (maximum 8, hence 91 different combinations from
one single cell to 8 together) on a userform. The user can choose
different combinations of those 8 cells, and I would like ideally the
Solver to be able to "read" those cells automatically. Hence the If
statements for each checkbox.

Dana, if I define the variable as "String", the code can not read my
variables (next to if statements) that are defined as Range.

I would appreciate any help as this is becoming a real urgent problem
for me.
Many thanks.

Jon Peltier
Macro for user defined Solver changing cells.
 
Don't use If/ElseIf, because the first If that is satisfied prevents the
rest from even being tested.

Try something like this to build a string representing the range.

Dim sRange as String

sRange = ""
If .CheckBox1.Value = True Then
sRange = sRange & "Cons" & ","
End If
If .CheckBox2.Value = True Then
sRange = sRange & "Nurse" & ","
End If
If .CheckBox3.Value = True Then
sRange = sRange & "Clinic" & ","
End If
If .CheckBox4.Value = True Then
sRange = sRange & "Admis" & ","
End If
If .CheckBox5.Value = True Then
sRange = sRange & "Other" & ","
End If
If .CheckBox6.Value = True Then
sRange = sRange & "MinorBasal" & ","
End If
If .CheckBox7.Value = True Then
sRange = sRange & "MajorBasal" & ","
End If
If .CheckBox8.Value = True Then
sRange = sRange & "PriceBasal" & ","
End If
sRange = Left$(sRange, Len(sRange)-1)

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
sRange


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
...
On 19 Dec, 16:04, "Dana DeLouis" wrote:
A message "Run time error 5, invalid procedure call or
argument" comes up.
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")

Hi. In your code segment above, only 1 cell is getting set. The other
cells are not set.
Your Solver code is trying to set the Changing Cells to all of them via
Union.
Is this the desired results?
Basically, the Union of a valid range, and "Nothing" is really nothing in
Excel, and therefore this is being passed to Solver.

If you mean to only pass 1 Valid Changing cell to Solver, perhaps an idea
like the following might help.

Sub Demo()
Dim ChgCell As String
'etc ...your code...

If .CheckBox1.Value Then
Set ChgCell = Range("Cons")
ElseIf .CheckBox2.Value Then
Set ChgCell = Range("Nurse")
ElseIf .CheckBox3.Value Then
Set ChgCell = Range("Clinic")
'etc

SolverOK SetCell:=....., ByChange:=ChgCell.Address
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007

wrote in message

...



On 18 Dec, 20:21, "Jon Peltier"
wrote:
Try changing the SolverOK command:

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8).Address

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

Can anyone please help me with this...
The code for a "User defined Solver" is not working :( and me
being
new in VBA can not work out why...Many thanks

Private Sub CommandButton7_Click()
Dim Cell1 As Range
Dim Cell2 As Range
Dim Cell3 As Range
Dim Cell4 As Range
Dim Cell5 As Range
Dim Cell6 As Range
Dim Cell7 As Range
Dim Cell8 As Range
With frmchecklist
.Show
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")
ElseIf .CheckBox3.Value = True Then
Set Cell3 = Range("Clinic")
ElseIf .CheckBox4.Value = True Then
Set Cell4 = Range("Admis")
ElseIf .CheckBox5.Value = True Then
Set Cell5 = Range("Other")
ElseIf .CheckBox6.Value = True Then
Set Cell6 = Range("MinorBasal")
ElseIf .CheckBox7.Value = True Then
Set Cell7 = Range("MajorBasal")
ElseIf .CheckBox8.Value = True Then
Set Cell8 = Range("PriceBasal")
End If
SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End With
End Sub- Hide quoted text -

- Show quoted text -

Thank you Jon for your time and suggestion. I tried this but it does
not work. A message "Run time error 5, invalid procedure call or
argument" comes up. Any other ideas? Many thanks.- Hide quoted text -

- Show quoted text -

Thank you for your reply. The idea is that the user can choose the
cells for which the solver will find a solution by clicking on
different checkboxes (maximum 8, hence 91 different combinations from
one single cell to 8 together) on a userform. The user can choose
different combinations of those 8 cells, and I would like ideally the
Solver to be able to "read" those cells automatically. Hence the If
statements for each checkbox.

Dana, if I define the variable as "String", the code can not read my
variables (next to if statements) that are defined as Range.

I would appreciate any help as this is becoming a real urgent problem
for me.
Many thanks.



[email protected]
Macro for user defined Solver changing cells.
 
On 20 Dec, 13:53, "Jon Peltier"
wrote:
Don't use If/ElseIf, because the first If that is satisfied prevents the
rest from even being tested.

Try something like this to build a string representing the range.

Dim sRange as String

sRange = ""
If .CheckBox1.Value = True Then
sRange = sRange & "Cons" & ","
End If
If .CheckBox2.Value = True Then
sRange = sRange & "Nurse" & ","
End If
If .CheckBox3.Value = True Then
sRange = sRange & "Clinic" & ","
End If
If .CheckBox4.Value = True Then
sRange = sRange & "Admis" & ","
End If
If .CheckBox5.Value = True Then
sRange = sRange & "Other" & ","
End If
If .CheckBox6.Value = True Then
sRange = sRange & "MinorBasal" & ","
End If
If .CheckBox7.Value = True Then
sRange = sRange & "MajorBasal" & ","
End If
If .CheckBox8.Value = True Then
sRange = sRange & "PriceBasal" & ","
End If
sRange = Left$(sRange, Len(sRange)-1)

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
sRange

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...



On 19 Dec, 16:04, "Dana DeLouis" wrote:
A message "Run time error 5, invalid procedure call or
argument" comes up.
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")

Hi. In your code segment above, only 1 cell is getting set. The other
cells are not set.
Your Solver code is trying to set the Changing Cells to all of them via
Union.
Is this the desired results?
Basically, the Union of a valid range, and "Nothing" is really nothing in
Excel, and therefore this is being passed to Solver.

If you mean to only pass 1 Valid Changing cell to Solver, perhaps an idea
like the following might help.

Sub Demo()
Dim ChgCell As String
'etc ...your code...

If .CheckBox1.Value Then
Set ChgCell = Range("Cons")
ElseIf .CheckBox2.Value Then
Set ChgCell = Range("Nurse")
ElseIf .CheckBox3.Value Then
Set ChgCell = Range("Clinic")
'etc

SolverOK SetCell:=....., ByChange:=ChgCell.Address
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007

wrote in message

...

On 18 Dec, 20:21, "Jon Peltier"
wrote:
Try changing the SolverOK command:

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8).Address

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

Can anyone please help me with this...
The code for a "User defined Solver" is not working :( and me
being
new in VBA can not work out why...Many thanks

Private Sub CommandButton7_Click()
Dim Cell1 As Range
Dim Cell2 As Range
Dim Cell3 As Range
Dim Cell4 As Range
Dim Cell5 As Range
Dim Cell6 As Range
Dim Cell7 As Range
Dim Cell8 As Range
With frmchecklist
.Show
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")
ElseIf .CheckBox3.Value = True Then
Set Cell3 = Range("Clinic")
ElseIf .CheckBox4.Value = True Then
Set Cell4 = Range("Admis")
ElseIf .CheckBox5.Value = True Then
Set Cell5 = Range("Other")
ElseIf .CheckBox6.Value = True Then
Set Cell6 = Range("MinorBasal")
ElseIf .CheckBox7.Value = True Then
Set Cell7 = Range("MajorBasal")
ElseIf .CheckBox8.Value = True Then
Set Cell8 = Range("PriceBasal")
End If
SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End With
End Sub- Hide quoted text -

- Show quoted text -

Thank you Jon for your time and suggestion. I tried this but it does
not work. A message "Run time error 5, invalid procedure call or
argument" comes up. Any other ideas? Many thanks.- Hide quoted text -

- Show quoted text -

Thank you for your reply. The idea is that the user can choose the
cells for which the solver will find a solution by clicking on
different checkboxes (maximum 8, hence 91 different combinations from
one single cell to 8 together) on a userform. The user can choose
different combinations of those 8 cells, and I would like ideally the
Solver to be able to "read" those cells automatically. Hence the If
statements for each checkbox.

Dana, if I define the variable as "String", the code can not read my
variables (next to if statements) that are defined as Range.

I would appreciate any help as this is becoming a real urgent problem
for me.
Many thanks.- Hide quoted text -

- Show quoted text -

Thank you for suggestion. I tried it and "invalid outside procedure"
comes up for sRange = "". Would creating a dynamic array (rather than
a range) be a solution... ?

[email protected]
Macro for user defined Solver changing cells.
 
On 20 Dec, 15:05, wrote:
On 20 Dec, 13:53, "Jon Peltier"
wrote:





Don't use If/ElseIf, because the first If that is satisfied prevents the
rest from even being tested.

Try something like this to build a string representing the range.

Dim sRange as String

sRange = ""
If .CheckBox1.Value = True Then
sRange = sRange & "Cons" & ","
End If
If .CheckBox2.Value = True Then
sRange = sRange & "Nurse" & ","
End If
If .CheckBox3.Value = True Then
sRange = sRange & "Clinic" & ","
End If
If .CheckBox4.Value = True Then
sRange = sRange & "Admis" & ","
End If
If .CheckBox5.Value = True Then
sRange = sRange & "Other" & ","
End If
If .CheckBox6.Value = True Then
sRange = sRange & "MinorBasal" & ","
End If
If .CheckBox7.Value = True Then
sRange = sRange & "MajorBasal" & ","
End If
If .CheckBox8.Value = True Then
sRange = sRange & "PriceBasal" & ","
End If
sRange = Left$(sRange, Len(sRange)-1)

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
sRange

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

On 19 Dec, 16:04, "Dana DeLouis" wrote:
A message "Run time error 5, invalid procedure call or
argument" comes up.
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")

Hi. In your code segment above, only 1 cell is getting set. The other
cells are not set.
Your Solver code is trying to set the Changing Cells to all of them via
Union.
Is this the desired results?
Basically, the Union of a valid range, and "Nothing" is really nothing in
Excel, and therefore this is being passed to Solver.

If you mean to only pass 1 Valid Changing cell to Solver, perhaps an idea
like the following might help.

Sub Demo()
Dim ChgCell As String
'etc ...your code...

If .CheckBox1.Value Then
Set ChgCell = Range("Cons")
ElseIf .CheckBox2.Value Then
Set ChgCell = Range("Nurse")
ElseIf .CheckBox3.Value Then
Set ChgCell = Range("Clinic")
'etc

SolverOK SetCell:=....., ByChange:=ChgCell.Address
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007

wrote in message

...

On 18 Dec, 20:21, "Jon Peltier"
wrote:
Try changing the SolverOK command:

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8).Address

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

Can anyone please help me with this...
The code for a "User defined Solver" is not working :( and me
being
new in VBA can not work out why...Many thanks

Private Sub CommandButton7_Click()
Dim Cell1 As Range
Dim Cell2 As Range
Dim Cell3 As Range
Dim Cell4 As Range
Dim Cell5 As Range
Dim Cell6 As Range
Dim Cell7 As Range
Dim Cell8 As Range
With frmchecklist
.Show
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")
ElseIf .CheckBox3.Value = True Then
Set Cell3 = Range("Clinic")
ElseIf .CheckBox4.Value = True Then
Set Cell4 = Range("Admis")
ElseIf .CheckBox5.Value = True Then
Set Cell5 = Range("Other")
ElseIf .CheckBox6.Value = True Then
Set Cell6 = Range("MinorBasal")
ElseIf .CheckBox7.Value = True Then
Set Cell7 = Range("MajorBasal")
ElseIf .CheckBox8.Value = True Then
Set Cell8 = Range("PriceBasal")
End If
SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End With
End Sub- Hide quoted text -

- Show quoted text -

Thank you Jon for your time and suggestion. I tried this but it does
not work. A message "Run time error 5, invalid procedure call or
argument" comes up. Any other ideas? Many thanks.- Hide quoted text -

- Show quoted text -

Thank you for your reply. The idea is that the user can choose the
cells for which the solver will find a solution by clicking on
different checkboxes (maximum 8, hence 91 different combinations from
one single cell to 8 together) on a userform. The user can choose
different combinations of those 8 cells, and I would like ideally the
Solver to be able to "read" those cells automatically. Hence the If
statements for each checkbox.

Dana, if I define the variable as "String", the code can not read my
variables (next to if statements) that are defined as Range.

I would appreciate any help as this is becoming a real urgent problem
for me.
Many thanks.- Hide quoted text -

- Show quoted text -

Thank you for suggestion. I tried it and "invalid outside procedure"
comes up for sRange = "". Would creating a dynamic array (rather than
a range) be a solution... ?- Hide quoted text -

- Show quoted text -

Sorry, got rid of the error message (something to do with Private Sub)
but code still not working :( :( :(

[email protected]
Macro for user defined Solver changing cells.
 
On 20 Dec, 15:08, wrote:
On 20 Dec, 15:05, wrote:





On 20 Dec, 13:53, "Jon Peltier"
wrote:

Don't use If/ElseIf, because the first If that is satisfied prevents the
rest from even being tested.

Try something like this to build a string representing the range.

Dim sRange as String

sRange = ""
If .CheckBox1.Value = True Then
sRange = sRange & "Cons" & ","
End If
If .CheckBox2.Value = True Then
sRange = sRange & "Nurse" & ","
End If
If .CheckBox3.Value = True Then
sRange = sRange & "Clinic" & ","
End If
If .CheckBox4.Value = True Then
sRange = sRange & "Admis" & ","
End If
If .CheckBox5.Value = True Then
sRange = sRange & "Other" & ","
End If
If .CheckBox6.Value = True Then
sRange = sRange & "MinorBasal" & ","
End If
If .CheckBox7.Value = True Then
sRange = sRange & "MajorBasal" & ","
End If
If .CheckBox8.Value = True Then
sRange = sRange & "PriceBasal" & ","
End If
sRange = Left$(sRange, Len(sRange)-1)

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
sRange

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

On 19 Dec, 16:04, "Dana DeLouis" wrote:
A message "Run time error 5, invalid procedure call or
argument" comes up.
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")

Hi. In your code segment above, only 1 cell is getting set. The other
cells are not set.
Your Solver code is trying to set the Changing Cells to all of them via
Union.
Is this the desired results?
Basically, the Union of a valid range, and "Nothing" is really nothing in
Excel, and therefore this is being passed to Solver.

If you mean to only pass 1 Valid Changing cell to Solver, perhaps an idea
like the following might help.

Sub Demo()
Dim ChgCell As String
'etc ...your code...

If .CheckBox1.Value Then
Set ChgCell = Range("Cons")
ElseIf .CheckBox2.Value Then
Set ChgCell = Range("Nurse")
ElseIf .CheckBox3.Value Then
Set ChgCell = Range("Clinic")
'etc

SolverOK SetCell:=....., ByChange:=ChgCell.Address
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007

wrote in message

...

On 18 Dec, 20:21, "Jon Peltier"
wrote:
Try changing the SolverOK command:

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8).Address

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

Can anyone please help me with this...
The code for a "User defined Solver" is not working :( and me
being
new in VBA can not work out why...Many thanks

Private Sub CommandButton7_Click()
Dim Cell1 As Range
Dim Cell2 As Range
Dim Cell3 As Range
Dim Cell4 As Range
Dim Cell5 As Range
Dim Cell6 As Range
Dim Cell7 As Range
Dim Cell8 As Range
With frmchecklist
.Show
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")
ElseIf .CheckBox3.Value = True Then
Set Cell3 = Range("Clinic")
ElseIf .CheckBox4.Value = True Then
Set Cell4 = Range("Admis")
ElseIf .CheckBox5.Value = True Then
Set Cell5 = Range("Other")
ElseIf .CheckBox6.Value = True Then
Set Cell6 = Range("MinorBasal")
ElseIf .CheckBox7.Value = True Then
Set Cell7 = Range("MajorBasal")
ElseIf .CheckBox8.Value = True Then
Set Cell8 = Range("PriceBasal")
End If
SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End With
End Sub- Hide quoted text -

- Show quoted text -

Thank you Jon for your time and suggestion. I tried this but it does
not work. A message "Run time error 5, invalid procedure call or
argument" comes up. Any other ideas? Many thanks.- Hide quoted text -

- Show quoted text -

Thank you for your reply. The idea is that the user can choose the
cells for which the solver will find a solution by clicking on
different checkboxes (maximum 8, hence 91 different combinations from
one single cell to 8 together) on a userform. The user can choose
different combinations of those 8 cells, and I would like ideally the
Solver to be able to "read" those cells automatically. Hence the If
statements for each checkbox.

Dana, if I define the variable as "String", the code can not read my
variables (next to if statements) that are defined as Range.

I would appreciate any help as this is becoming a real urgent problem
for me.
Many thanks.- Hide quoted text -

- Show quoted text -

Thank you for suggestion. I tried it and "invalid outside procedure"
comes up for sRange = "". Would creating a dynamic array (rather than
a range) be a solution... ?- Hide quoted text -

- Show quoted text -

Sorry, got rid of the error message (something to do with Private Sub)
but code still not working :( :( :(- Hide quoted text -

- Show quoted text -

Hi again,
I think I know why the solver does not work...in the ByChange field it
requires a "range" variable rather than string and sRange is a string
variable. So, sRange that we have defined "says" nothing to Solver.

Jon Peltier
Macro for user defined Solver changing cells.
 
I just recorded a macro while running a simple Solver exercise. The macro I
recorded looked like this:

Sub SolveByAddress()
SolverOk SetCell:="$B$6", MaxMinVal:=1, ValueOf:="0",
ByChange:="$B$3,$B$4"
SolverAdd CellRef:="$B$3", Relation:=1, FormulaText:="4"
SolverAdd CellRef:="$B$4", Relation:=1, FormulaText:="4"
SolverOk SetCell:="$B$6", MaxMinVal:=1, ValueOf:="0",
ByChange:="$B$3,$B$4"
SolverSolve
End Sub

I amended the macro to use names:

Sub SolveByName()
SolverAdd CellRef:="first", Relation:=1, FormulaText:="4"
SolverAdd CellRef:="last", Relation:=1, FormulaText:="4"
SolverOk SetCell:="$B$6", MaxMinVal:=1, ValueOf:="0",
ByChange:="first,last"
SolverSolve
End Sub

The macros work exactly the same way. Solver requires only string inputs,
not ranges.

To validate the code I suggested, I tried this, and it also worked fine:

Sub SolveByName2()
Dim sRange As String
sRange = ""
sRange = sRange & "first,"
sRange = sRange & "last,"
sRange = Left$(sRange, Len(sRange) - 1)

SolverAdd CellRef:="first", Relation:=1, FormulaText:="4"
SolverAdd CellRef:="last", Relation:=1, FormulaText:="4"
SolverOk SetCell:="$B$6", MaxMinVal:=1, ValueOf:="0", ByChange:=sRange
SolverSolve
End Sub

I don't know what else might be going on.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
...
On 20 Dec, 15:08, wrote:
On 20 Dec, 15:05, wrote:





On 20 Dec, 13:53, "Jon Peltier"
wrote:

Don't use If/ElseIf, because the first If that is satisfied prevents
the
rest from even being tested.

Try something like this to build a string representing the range.

Dim sRange as String

sRange = ""
If .CheckBox1.Value = True Then
sRange = sRange & "Cons" & ","
End If
If .CheckBox2.Value = True Then
sRange = sRange & "Nurse" & ","
End If
If .CheckBox3.Value = True Then
sRange = sRange & "Clinic" & ","
End If
If .CheckBox4.Value = True Then
sRange = sRange & "Admis" & ","
End If
If .CheckBox5.Value = True Then
sRange = sRange & "Other" & ","
End If
If .CheckBox6.Value = True Then
sRange = sRange & "MinorBasal" & ","
End If
If .CheckBox7.Value = True Then
sRange = sRange & "MajorBasal" & ","
End If
If .CheckBox8.Value = True Then
sRange = sRange & "PriceBasal" & ","
End If
sRange = Left$(sRange, Len(sRange)-1)

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
sRange

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

On 19 Dec, 16:04, "Dana DeLouis" wrote:
A message "Run time error 5, invalid procedure call or
argument" comes up.
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")

Hi. In your code segment above, only 1 cell is getting set. The
other
cells are not set.
Your Solver code is trying to set the Changing Cells to all of
them via
Union.
Is this the desired results?
Basically, the Union of a valid range, and "Nothing" is really
nothing in
Excel, and therefore this is being passed to Solver.

If you mean to only pass 1 Valid Changing cell to Solver, perhaps
an idea
like the following might help.

Sub Demo()
Dim ChgCell As String
'etc ...your code...

If .CheckBox1.Value Then
Set ChgCell = Range("Cons")
ElseIf .CheckBox2.Value Then
Set ChgCell = Range("Nurse")
ElseIf .CheckBox3.Value Then
Set ChgCell = Range("Clinic")
'etc

SolverOK SetCell:=....., ByChange:=ChgCell.Address
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007

wrote in message

...

On 18 Dec, 20:21, "Jon Peltier"
wrote:
Try changing the SolverOK command:

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0",
ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7,
Cell8).Address

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

Can anyone please help me with this...
The code for a "User defined Solver" is not working :( and
me
being
new in VBA can not work out why...Many thanks

Private Sub CommandButton7_Click()
Dim Cell1 As Range
Dim Cell2 As Range
Dim Cell3 As Range
Dim Cell4 As Range
Dim Cell5 As Range
Dim Cell6 As Range
Dim Cell7 As Range
Dim Cell8 As Range
With frmchecklist
.Show
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")
ElseIf .CheckBox3.Value = True Then
Set Cell3 = Range("Clinic")
ElseIf .CheckBox4.Value = True Then
Set Cell4 = Range("Admis")
ElseIf .CheckBox5.Value = True Then
Set Cell5 = Range("Other")
ElseIf .CheckBox6.Value = True Then
Set Cell6 = Range("MinorBasal")
ElseIf .CheckBox7.Value = True Then
Set Cell7 = Range("MajorBasal")
ElseIf .CheckBox8.Value = True Then
Set Cell8 = Range("PriceBasal")
End If
SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0",
ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End With
End Sub- Hide quoted text -

- Show quoted text -

Thank you Jon for your time and suggestion. I tried this but it
does
not work. A message "Run time error 5, invalid procedure call or
argument" comes up. Any other ideas? Many thanks.- Hide quoted
text -

- Show quoted text -

Thank you for your reply. The idea is that the user can choose the
cells for which the solver will find a solution by clicking on
different checkboxes (maximum 8, hence 91 different combinations
from
one single cell to 8 together) on a userform. The user can choose
different combinations of those 8 cells, and I would like ideally
the
Solver to be able to "read" those cells automatically. Hence the If
statements for each checkbox.

Dana, if I define the variable as "String", the code can not read
my
variables (next to if statements) that are defined as Range.

I would appreciate any help as this is becoming a real urgent
problem
for me.
Many thanks.- Hide quoted text -

- Show quoted text -

Thank you for suggestion. I tried it and "invalid outside procedure"
comes up for sRange = "". Would creating a dynamic array (rather than
a range) be a solution... ?- Hide quoted text -

- Show quoted text -

Sorry, got rid of the error message (something to do with Private Sub)
but code still not working :( :( :(- Hide quoted text -

- Show quoted text -

Hi again,
I think I know why the solver does not work...in the ByChange field it
requires a "range" variable rather than string and sRange is a string
variable. So, sRange that we have defined "says" nothing to Solver.



[email protected]
Macro for user defined Solver changing cells.
 
On 21 Dec, 03:27, "Jon Peltier"
wrote:
I just recorded a macro while running a simple Solver exercise. The macro I
recorded looked like this:

Sub SolveByAddress()
* * SolverOk SetCell:="$B$6", MaxMinVal:=1, ValueOf:="0",
ByChange:="$B$3,$B$4"
* * SolverAdd CellRef:="$B$3", Relation:=1, FormulaText:="4"
* * SolverAdd CellRef:="$B$4", Relation:=1, FormulaText:="4"
* * SolverOk SetCell:="$B$6", MaxMinVal:=1, ValueOf:="0",
ByChange:="$B$3,$B$4"
* * SolverSolve
End Sub

I amended the macro to use names:

Sub SolveByName()
* * SolverAdd CellRef:="first", Relation:=1, FormulaText:="4"
* * SolverAdd CellRef:="last", Relation:=1, FormulaText:="4"
* * SolverOk SetCell:="$B$6", MaxMinVal:=1, ValueOf:="0",
ByChange:="first,last"
* * SolverSolve
End Sub

The macros work exactly the same way. Solver requires only string inputs,
not ranges.

To validate the code I suggested, I tried this, and it also worked fine:

Sub SolveByName2()
* * Dim sRange As String
* * sRange = ""
* * sRange = sRange & "first,"
* * sRange = sRange & "last,"
* * sRange = Left$(sRange, Len(sRange) - 1)

* * SolverAdd CellRef:="first", Relation:=1, FormulaText:="4"
* * SolverAdd CellRef:="last", Relation:=1, FormulaText:="4"
* * SolverOk SetCell:="$B$6", MaxMinVal:=1, ValueOf:="0", ByChange:=sRange
* * SolverSolve
End Sub

I don't know what else might be going on.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...



On 20 Dec, 15:08, wrote:
On 20 Dec, 15:05, wrote:

On 20 Dec, 13:53, "Jon Peltier"
wrote:

Don't use If/ElseIf, because the first If that is satisfied prevents
the
rest from even being tested.

Try something like this to build a string representing the range.

Dim sRange as String

sRange = ""
If .CheckBox1.Value = True Then
* sRange = sRange & "Cons" & ","
End If
If .CheckBox2.Value = True Then
* sRange = sRange & "Nurse" & ","
End If
If .CheckBox3.Value = True Then
* sRange = sRange & "Clinic" & ","
End If
If .CheckBox4.Value = True Then
* sRange = sRange & "Admis" & ","
End If
If .CheckBox5.Value = True Then
* sRange = sRange & "Other" & ","
End If
If .CheckBox6.Value = True Then
* sRange = sRange & "MinorBasal" & ","
End If
If .CheckBox7.Value = True Then
* sRange = sRange & "MajorBasal" & ","
End If
If .CheckBox8.Value = True Then
* sRange = sRange & "PriceBasal" & ","
End If
sRange = Left$(sRange, Len(sRange)-1)

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0", ByChange:= _
* * sRange

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

On 19 Dec, 16:04, "Dana DeLouis" wrote:
A message "Run time error 5, invalid procedure call or
argument" comes up.
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")

Hi. *In your code segment above, only 1 cell is getting set. *The
other
cells are not set.
Your Solver code is trying to set the Changing Cells to all of
them via
Union.
Is this the desired results?
Basically, the Union of a valid range, and "Nothing" is really
nothing in
Excel, and therefore this is being passed to Solver.

If you mean to only pass 1 Valid Changing cell to Solver, perhaps
an idea
like the following might help.

Sub Demo()
Dim ChgCell As String
'etc ...your code...

If .CheckBox1.Value Then
* Set ChgCell = Range("Cons")
ElseIf .CheckBox2.Value Then
* Set ChgCell = Range("Nurse")
ElseIf .CheckBox3.Value Then
* Set ChgCell = Range("Clinic")
'etc

SolverOK SetCell:=....., ByChange:=ChgCell.Address
End Sub

--
HTH * :)
Dana DeLouis
Windows XP & Excel 2007

wrote in message

...

On 18 Dec, 20:21, "Jon Peltier"
wrote:
Try changing the SolverOK command:

SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0",
ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7,
Cell8).Address

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

Can anyone please help me with this...
The code for a "User defined Solver" *is not working :( *and
me
being
new in VBA can not work out why...Many thanks

Private Sub CommandButton7_Click()
Dim Cell1 As Range
Dim Cell2 As Range
Dim Cell3 As Range
Dim Cell4 As Range
Dim Cell5 As Range
Dim Cell6 As Range
Dim Cell7 As Range
Dim Cell8 As Range
With frmchecklist
.Show
If .CheckBox1.Value = True Then
Set Cell1 = Range("Cons")
ElseIf .CheckBox2.Value = True Then
Set Cell2 = Range("Nurse")
ElseIf .CheckBox3.Value = True Then
Set Cell3 = Range("Clinic")
ElseIf .CheckBox4.Value = True Then
Set Cell4 = Range("Admis")
ElseIf .CheckBox5.Value = True Then
Set Cell5 = Range("Other")
ElseIf .CheckBox6.Value = True Then
Set Cell6 = Range("MinorBasal")
ElseIf .CheckBox7.Value = True Then
Set Cell7 = Range("MajorBasal")
ElseIf .CheckBox8.Value = True Then
Set Cell8 = Range("PriceBasal")
End If
SolverOK SetCell:="$K$22", MaxMinVal:=3, ValueOf:="0",
ByChange:= _
Union(Cell1, Cell2, Cell3, Cell4, Cell5, Cell6, Cell7, Cell8)
* * * *SolverSolve UserFinish:=True
* * * *SolverFinish KeepFinal:=1
End With
End Sub- Hide quoted text -

- Show quoted text -

Thank you Jon for your time and suggestion. I tried this but it
does
not work. A message "Run time error 5, invalid procedure call or
argument" comes up. Any other ideas? Many thanks.- Hide quoted
text -

- Show quoted text -

Thank you for your reply. The idea is that the user can choose the
cells for which the solver will find a solution by clicking on
different checkboxes (maximum 8, hence 91 different combinations
from
one single cell to 8 together) on a userform. The user can choose
different combinations of those 8 cells, and I would like ideally
the
Solver to be able to "read" those cells automatically. Hence the If
statements for each checkbox.

Dana, if I define the variable as "String", the code can not read
my
variables (next to if statements) that are defined as Range.

I would appreciate any help as this is becoming a real urgent
problem
for me.
Many thanks.- Hide quoted text -

- Show quoted text -

Thank you for suggestion. I tried it and "invalid outside procedure"
comes up for sRange = "". Would creating a dynamic array (rather than
a range) be a solution... ?- Hide quoted text -

- Show quoted text -

Sorry, got rid of the error message (something to do with Private Sub)
but code still not working :( :( :(- Hide quoted text -

- Show quoted text -

Hi again,
I think I know why the solver does not work...in the ByChange field it
requires a "range" variable rather than string and sRange is a string
variable. So, sRange that we have defined "says" nothing to Solver.- Hide quoted text -

- Show quoted text -

Hi Jon,
Works like a dream :) :) Many, many thanks for your help. Made my
day :)
Happy Christmas!


All times are GMT +1. The time now is 12:32 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com