Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro for user defined Solver changing cells.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,582
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default 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.
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,582
Default 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.


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 947
Default 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.


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default 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.
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,582
Default 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.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro for Solver with user defined changing cells [email protected] Excel Programming 0 December 18th 07 10:31 AM
Excel Solver as User Defined Funcntion Murthy Excel Worksheet Functions 11 March 3rd 07 08:16 PM
Trigger Excel Solver by User Defined Function Murthy Excel Programming 0 March 2nd 07 11:29 PM
Solver in a User-Defined Function Pflugs Excel Programming 4 July 14th 06 11:27 PM
changing nested if statements into a user defined function Rob Slagle Excel Programming 3 July 23rd 04 07:04 PM


All times are GMT +1. The time now is 03:26 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement