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" <(E-Mail Removed)> wrote in message
news:5E310068-B7F9-41BA-A333-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:5E310068-B7F9-41BA-A333-(E-Mail Removed)...
> >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. 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" <(E-Mail Removed)> wrote in message
news5D71A24-2752-4F7B-8C17-(E-Mail Removed)...
> 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
>
> *****
<snip>

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news5D71A24-2752-4F7B-8C17-(E-Mail Removed)...
> > 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
> >
> > *****
> <snip>
>
>
>

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.

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news5D71A24-2752-4F7B-8C17-(E-Mail Removed)...
> > 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
> >
> > *****
> <snip>
>
>
>

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

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. :>~

--
Dana DeLouis