Macro for user defined Solver changing cells.

[genard]

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]

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

 

[genard]

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
_______







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

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

 

[Dana DeLouis]

A message "Run time error 5, invalid procedure call or

argument" comes up.

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

 

[genard]

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







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

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

 

[genard]

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
_______









- 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... ?

 

[genard]

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

 

[genard]

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]

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

 

[genard]

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
_______







- Show quoted text -

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