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

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



"JohnTReed" wrote:

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

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.

"driller" wrote:

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



"JohnTReed" wrote:

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

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



"JohnTReed" wrote:

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.

"driller" wrote:

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



"JohnTReed" wrote:

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

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

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

...but I need to do it to many columns.


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

--
Dana DeLouis

"driller" wrote in message
...
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



"JohnTReed" wrote:

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.

"driller" wrote:

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



"JohnTReed" wrote:

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

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.

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



"Dana DeLouis" wrote:

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

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

...but I need to do it to many columns.


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

--
Dana DeLouis

"driller" wrote in message
...
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



"JohnTReed" wrote:

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.

"driller" wrote:

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



"JohnTReed" wrote:

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

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



"JohnTReed" wrote:

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