How do I get solver to excute more than one time

G

Guest

Using Solver in VBA I created the following little macro:

Sub SolverMacro()
' My Example of a Solver VBA Macro using variables
Dim rng1 As Variant
Dim rng2 As Variant
Dim rng3 As Variant

rng1 = "$BL$11:$BL$58"
rng2 = "$BR$58"
rng3 = "$BL$11:$BL$58"

SolverReset
SolverOk SetCell:=rng1, _
MaxMinVal:=3, _
ValueOf:="0", _
ByChange:=rng2
SolverAdd CellRef:=rng3, Relation:=3, FormulaText:="0"
SolverSolve
End Sub

Needless to say it would not repeat, it would go thru the code but change
nothing. I then created a macro with the macro recoder:
Sub Macro2()
SolverOk SetCell:="$BR$58", MaxMinVal:=1, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverSolve
End Sub
However like the first macro it would not repeat. When I try to use it again
it goes thru the routine but changes nothing. I made manual changes to the
range "$BL$11:$BL$58" to check if the macro was working and it changed nothing

When I use the solver by manually entering the data into the solver fields
it works ok. What am I doing wrong and how do I get Solver to work more than
time using VBA
 
G

Guest

Hi JohnTReed,

Just for a starter...
I'll just pass the suggestion from someone, try to "tame the beast in the
sheet" with or without the macro, ...by any ways that the group may suggest..


--
regards,
driller

*****
- dive with Jonathan Seagull
 
G

Guest

Thanks for your suggestion.
As you can see it is not a very complicated but I need to do it to many
columns. I thought I would use a menu so that the user could choose the
column to use the solver on. That is why I was using the variables.

I was going to use the select case to choose the columns to insert into the
solver. I was testing out the concept and noticed that when I ran the solver
again nothing changed.

I am at a loss as to why. I understand that solver doesn't like complex
models but what I am doing is no that complex.
 
G

Guest

i run the code and also try manual input while recording with good result,
yet same thing happens, cannot run-back to the recorded macro...
its time for good macroman to step-in here for a kind test and simple
suggestion...

just hoping someone have the solution we need...

--
regards,
driller

*****
- dive with Jonathan Seagull
 
D

Dana DeLouis

rng1 = "$BL$11:$BL$58"

Hello. I can't follow the logic of your recorded macro.
The Solver's Target cell is usually 1 cell, yet you have a range BL11:BL58.
Solver usually will see an error, and quit without much warning.

Your macro says, in general
Set Target = 0
subject to
Target >=0.

The constraint appears to be in conflict with the Target. You don't really
want it >0 since you want the Target cell =0.

Did you actually mean that you want each cell in BL11: BL58 to be a target,
and run each time ??

I can't test this of course, so I'll just throw this out as an idea. You
may need to add constraints.

Sub SolverMacro()
Dim rng1 As Range
Dim rng2 As String
Dim Cell As Range

Set rng1 = "BL11:BL58"
rng2 = "BR58"

For Each Cell In rng1.Cells
SolverReset
SolverOk SetCell:=Cell.Address, _
MaxMinVal:=3, _
ValueOf:="0", _
ByChange:=rng2
SolverSolve True
Next Cell
End Sub
 
G

Guest

Hi DanaDL,

really, i like the style you throw suggestions!
I am at a loss as to why. I understand that solver doesn't like complex
models but what I am doing is no that complex.
Click to expand...

the way i read it, the 2 scenario of his code..one is in en-coding...and the
other one is in record then run by mouseclicks...

As a plain excel user, i am very interested to hear your suggestion on the
*Record* then *Run* procedure to call Solver with printtab reports <here's
the essence>.

I have record the same scheme with a little more detailed input until i see
*an optimal result*..acceptable,
then request for printtab report, then finally stop the recording.

then i have the same thing in my computer with VB window popping up
everytime i run the record, not even halfway from the record..

do we have any update *How-To* about this problem?


Sub DanaDL()
'
' DanaDL Macro
' Macro recorded 7/19/2007 by driller
'
' Keyboard Shortcut: Ctrl+d
'
Application.Goto Reference:="R58C70"

'=BR58

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-47]C[-6]:RC[-6])"

'=average(bL11:bL58)

Application.Goto Reference:="R11C64"
ActiveCell.FormulaR1C1 = "1"
Range("BL11").Select
Selection.Copy
Range("BL12:BL58").Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Above : bL11:bL58=1, initial result BR58 = 1,

Range("BR58").Select

'REQUEST SOLVER for BR58=0,
'with constraints like =(bL11:bL58<=5)*(bL11:bL58=int)*(bL11:bL58=>-5)

'*Pop-Up here when the macro runs: **VB ! Compile error: Sub or Function not
defined <press OK or Help)

'*SolverOK* highlighted <g> maybe this is a too sensitive word in the
macroworld to place OK...

SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverAdd CellRef:="$BL$11:$BL$58", Relation:=1, FormulaText:="5"
SolverAdd CellRef:="$BL$11:$BL$58", Relation:=3, FormulaText:="-5"
SolverAdd CellRef:="$BL$11:$BL$58", Relation:=4, FormulaText:="integer"
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=True, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=True, Convergence:=0.0001,
AssumeNonNeg:=False
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverSolve
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=True, Convergence:=0.0001,
AssumeNonNeg:=False
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverSolve
Selection.NumberFormat = "0"

' i dont know where goes the recorded command about the auto-generated tab
reports...(i.e. Answer Report)

ActiveWorkbook.Save
End Sub
____________________
'Q? is there a solution to just record these without any additional VB
editing..., i guess John Treed want to get his task done faster by Excel.
--
regards,
driller

*****
- dive with Jonathan Seagull
 
D

Dana DeLouis

Hi. Can't quite follow, so here are some more guesses.
In the vba editor, go to TOOLS | REFERENCE, and select "Solver."
Make sure your workbook is in A1 format, and not using R1C1. Latest vesions
of Solver only work in A1 notation.
There appears to be more bugs when I record a macro. See if this works for
you instead.

Sub Demo()
[BR58].Formula = "=AVERAGE(BL11:BL58)"
[BL11:BL58] = 1
SolverReset
SolverOk "$BR$58", 3, 0, "$BL$11:$BL$58"
SolverAdd "$BL$11:$BL$58", 1, 5
SolverAdd "$BL$11:$BL$58", 3, -5
SolverAdd "$BL$11:$BL$58", 4

'SolverOptions -> Your options are ok...

SolverSolve True '<- No Pop-up
SolverFinish 1, Array(1, 2, 3) '<- All 3 Reports
End Sub


Without knowing the rest of your model, my best guess is that finding an
Average of 0 over many cells is not the best idea. This is because it has
many solutions. For example, the average of -3,+3 is 0, as well
as -8,+3,+5.
Using "Average" as a Target function usually is a "flag" for an error. But
that's just my experience. Your model may be set up ok.

--
HTH :>)
Dana DeLouis


driller said:
Hi DanaDL,

really, i like the style you throw suggestions!
I am at a loss as to why. I understand that solver doesn't like complex
models but what I am doing is no that complex.
Click to expand...

the way i read it, the 2 scenario of his code..one is in en-coding...and
the
other one is in record then run by mouseclicks...

As a plain excel user, i am very interested to hear your suggestion on the
*Record* then *Run* procedure to call Solver with printtab reports <here's
the essence>.

I have record the same scheme with a little more detailed input until i
see
*an optimal result*..acceptable,
then request for printtab report, then finally stop the recording.

then i have the same thing in my computer with VB window popping up
everytime i run the record, not even halfway from the record..

do we have any update *How-To* about this problem?


Sub DanaDL()
'
' DanaDL Macro
' Macro recorded 7/19/2007 by driller
'
' Keyboard Shortcut: Ctrl+d
'
Application.Goto Reference:="R58C70"

'=BR58

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-47]C[-6]:RC[-6])"

'=average(bL11:bL58)

Application.Goto Reference:="R11C64"
ActiveCell.FormulaR1C1 = "1"
Range("BL11").Select
Selection.Copy
Range("BL12:BL58").Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Above : bL11:bL58=1, initial result BR58 = 1,

Range("BR58").Select

'REQUEST SOLVER for BR58=0,
'with constraints like =(bL11:bL58<=5)*(bL11:bL58=int)*(bL11:bL58=>-5)

'*Pop-Up here when the macro runs: **VB ! Compile error: Sub or Function
not
defined <press OK or Help)

'*SolverOK* highlighted <g> maybe this is a too sensitive word in the
macroworld to place OK...

SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverAdd CellRef:="$BL$11:$BL$58", Relation:=1, FormulaText:="5"
SolverAdd CellRef:="$BL$11:$BL$58", Relation:=3, FormulaText:="-5"
SolverAdd CellRef:="$BL$11:$BL$58", Relation:=4, FormulaText:="integer"
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=True, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=True, Convergence:=0.0001,
AssumeNonNeg:=False
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverSolve
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=True, Convergence:=0.0001,
AssumeNonNeg:=False
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverSolve
Selection.NumberFormat = "0"

' i dont know where goes the recorded command about the auto-generated tab
reports...(i.e. Answer Report)

ActiveWorkbook.Save
End Sub
____________________
'Q? is there a solution to just record these without any additional VB
editing..., i guess John Treed want to get his task done faster by Excel.
--
regards,
driller

*****
Click to expand...
<snip>
 
G

Guest

Hi DanaDL,

thanks!

very interesting....the build-up is quite new and confusing...whats the
meaning between the lines of that code to learn which are the adjustable
lines or characters or whatever....kind of option statements...

its a headache now for me not taking attention during my schooldays <about
language>...i almost drop-out from this comp.subjects...whew years ago...

never thought I'll end up typing infront of it.

have good day !

--
regards,
driller

*****
- dive with Jonathan Seagull



Dana DeLouis said:
Hi. Can't quite follow, so here are some more guesses.
In the vba editor, go to TOOLS | REFERENCE, and select "Solver."
Make sure your workbook is in A1 format, and not using R1C1. Latest vesions
of Solver only work in A1 notation.
There appears to be more bugs when I record a macro. See if this works for
you instead.

Sub Demo()
[BR58].Formula = "=AVERAGE(BL11:BL58)"
[BL11:BL58] = 1
SolverReset
SolverOk "$BR$58", 3, 0, "$BL$11:$BL$58"
SolverAdd "$BL$11:$BL$58", 1, 5
SolverAdd "$BL$11:$BL$58", 3, -5
SolverAdd "$BL$11:$BL$58", 4

'SolverOptions -> Your options are ok...

SolverSolve True '<- No Pop-up
SolverFinish 1, Array(1, 2, 3) '<- All 3 Reports
End Sub


Without knowing the rest of your model, my best guess is that finding an
Average of 0 over many cells is not the best idea. This is because it has
many solutions. For example, the average of -3,+3 is 0, as well
as -8,+3,+5.
Using "Average" as a Target function usually is a "flag" for an error. But
that's just my experience. Your model may be set up ok.

--
HTH :>)
Dana DeLouis


driller said:
Hi DanaDL,

really, i like the style you throw suggestions!
I am at a loss as to why. I understand that solver doesn't like complex
models but what I am doing is no that complex.
Click to expand...

the way i read it, the 2 scenario of his code..one is in en-coding...and
the
other one is in record then run by mouseclicks...

As a plain excel user, i am very interested to hear your suggestion on the
*Record* then *Run* procedure to call Solver with printtab reports <here's
the essence>.

I have record the same scheme with a little more detailed input until i
see
*an optimal result*..acceptable,
then request for printtab report, then finally stop the recording.

then i have the same thing in my computer with VB window popping up
everytime i run the record, not even halfway from the record..

do we have any update *How-To* about this problem?


Sub DanaDL()
'
' DanaDL Macro
' Macro recorded 7/19/2007 by driller
'
' Keyboard Shortcut: Ctrl+d
'
Application.Goto Reference:="R58C70"

'=BR58

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-47]C[-6]:RC[-6])"

'=average(bL11:bL58)

Application.Goto Reference:="R11C64"
ActiveCell.FormulaR1C1 = "1"
Range("BL11").Select
Selection.Copy
Range("BL12:BL58").Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Above : bL11:bL58=1, initial result BR58 = 1,

Range("BR58").Select

'REQUEST SOLVER for BR58=0,
'with constraints like =(bL11:bL58<=5)*(bL11:bL58=int)*(bL11:bL58=>-5)

'*Pop-Up here when the macro runs: **VB ! Compile error: Sub or Function
not
defined <press OK or Help)

'*SolverOK* highlighted <g> maybe this is a too sensitive word in the
macroworld to place OK...

SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverAdd CellRef:="$BL$11:$BL$58", Relation:=1, FormulaText:="5"
SolverAdd CellRef:="$BL$11:$BL$58", Relation:=3, FormulaText:="-5"
SolverAdd CellRef:="$BL$11:$BL$58", Relation:=4, FormulaText:="integer"
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=True, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=True, Convergence:=0.0001,
AssumeNonNeg:=False
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverSolve
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=True, Convergence:=0.0001,
AssumeNonNeg:=False
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverSolve
Selection.NumberFormat = "0"

' i dont know where goes the recorded command about the auto-generated tab
reports...(i.e. Answer Report)

ActiveWorkbook.Save
End Sub
____________________
'Q? is there a solution to just record these without any additional VB
editing..., i guess John Treed want to get his task done faster by Excel.
--
regards,
driller

*****
Click to expand...
<snip>
Click to expand...
 
G

Guest

Dear Dana,

really thanks for your time and effort...
There appears to be more bugs when I record a macro. See if this works for
you instead.
Click to expand...

i tested for few times yet the same error still persist ! in this line...
* SolverReset *.

thanks for the advice..

--
regards,
driller

*****
- dive with Jonathan Seagull



Dana DeLouis said:
Hi. Can't quite follow, so here are some more guesses.
In the vba editor, go to TOOLS | REFERENCE, and select "Solver."
Make sure your workbook is in A1 format, and not using R1C1. Latest vesions
of Solver only work in A1 notation.
There appears to be more bugs when I record a macro. See if this works for
you instead.

Sub Demo()
[BR58].Formula = "=AVERAGE(BL11:BL58)"
[BL11:BL58] = 1
SolverReset
SolverOk "$BR$58", 3, 0, "$BL$11:$BL$58"
SolverAdd "$BL$11:$BL$58", 1, 5
SolverAdd "$BL$11:$BL$58", 3, -5
SolverAdd "$BL$11:$BL$58", 4

'SolverOptions -> Your options are ok...

SolverSolve True '<- No Pop-up
SolverFinish 1, Array(1, 2, 3) '<- All 3 Reports
End Sub


Without knowing the rest of your model, my best guess is that finding an
Average of 0 over many cells is not the best idea. This is because it has
many solutions. For example, the average of -3,+3 is 0, as well
as -8,+3,+5.
Using "Average" as a Target function usually is a "flag" for an error. But
that's just my experience. Your model may be set up ok.

--
HTH :>)
Dana DeLouis


driller said:
Hi DanaDL,

really, i like the style you throw suggestions!
I am at a loss as to why. I understand that solver doesn't like complex
models but what I am doing is no that complex.
Click to expand...

the way i read it, the 2 scenario of his code..one is in en-coding...and
the
other one is in record then run by mouseclicks...

As a plain excel user, i am very interested to hear your suggestion on the
*Record* then *Run* procedure to call Solver with printtab reports <here's
the essence>.

I have record the same scheme with a little more detailed input until i
see
*an optimal result*..acceptable,
then request for printtab report, then finally stop the recording.

then i have the same thing in my computer with VB window popping up
everytime i run the record, not even halfway from the record..

do we have any update *How-To* about this problem?


Sub DanaDL()
'
' DanaDL Macro
' Macro recorded 7/19/2007 by driller
'
' Keyboard Shortcut: Ctrl+d
'
Application.Goto Reference:="R58C70"

'=BR58

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-47]C[-6]:RC[-6])"

'=average(bL11:bL58)

Application.Goto Reference:="R11C64"
ActiveCell.FormulaR1C1 = "1"
Range("BL11").Select
Selection.Copy
Range("BL12:BL58").Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Above : bL11:bL58=1, initial result BR58 = 1,

Range("BR58").Select

'REQUEST SOLVER for BR58=0,
'with constraints like =(bL11:bL58<=5)*(bL11:bL58=int)*(bL11:bL58=>-5)

'*Pop-Up here when the macro runs: **VB ! Compile error: Sub or Function
not
defined <press OK or Help)

'*SolverOK* highlighted <g> maybe this is a too sensitive word in the
macroworld to place OK...

SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverAdd CellRef:="$BL$11:$BL$58", Relation:=1, FormulaText:="5"
SolverAdd CellRef:="$BL$11:$BL$58", Relation:=3, FormulaText:="-5"
SolverAdd CellRef:="$BL$11:$BL$58", Relation:=4, FormulaText:="integer"
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=True, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=True, Convergence:=0.0001,
AssumeNonNeg:=False
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverSolve
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=True, Convergence:=0.0001,
AssumeNonNeg:=False
SolverOk SetCell:="$BR$58", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$BL$11:$BL$58"
SolverSolve
Selection.NumberFormat = "0"

' i dont know where goes the recorded command about the auto-generated tab
reports...(i.e. Answer Report)

ActiveWorkbook.Save
End Sub
____________________
'Q? is there a solution to just record these without any additional VB
editing..., i guess John Treed want to get his task done faster by Excel.
--
regards,
driller

*****
Click to expand...
<snip>
Click to expand...
 
D

Dana DeLouis

i tested for few times yet the same error still persist ! in this line...
* SolverReset *.
Click to expand...

Hi. It should work. Is the error "Sub or Function not defined." ?

If so, go to the worksheet, and select Solver. This just starts the
program. Close Solver.
Go to the vba editor, and select Tools | References, and Select Solver.
It should work now. :>~
 
G

Guest

Dear DanaDL

You're a beauty with the beast !

Can I invite you for a coffee... i have only 2 cents here but this coin is
fashioned for centuries ago...

You allow me to see the other side of the show... Now...

I am just so thrilled getting results from my own effort...
yet, with your guidance, its a must on me to always be here... Updated!!!

I hope u can have some *answers* too from my Post in the Worksheet Function
group...my post is isolated there with a subject title of
*"how can I compensate this...pleass contribute..."*

this have a little *Save-As* twist which needs some enlightenment for plain
users like me..i request for an update about the *iteration* with *automatic*
calculation mode...for cloned books...

sincerely
--
regards,
driller
romelsb
dribler2
*****
- dive with Jonathan Seagull
 
G

Guest

thanks John for allowing me to enter here...
*yet your question might not yet been solved*
mine is OK already, thanks to Ms. Dana...

see you on your next post..
--
regards,
driller
Plain User
*****
- dive with Jonathan Seagull
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I Use Solver with the Macro Recoder/VBA 1
Solver iteration in VBA macro 2
Solver marco still prompts to keep solution 1
Solver Problem 1
Another solver question 1
Solver Macro in Excel 2007 1
VBA code to RUN Solver mutiple times (on different target cells) 8
Excel Solver Macro: Contraints Get Lost! - Please Help 4

Top