Hi,

I'm having some issues with Solver -- while I have found workarounds to
resolve the issue, I have no idea as to why the code needed to be changed and
would like to know if anyone has any ideas. What is really driving me crazy
is that the original code was working fine until a couple of days ago -- I
didn't change any code in the module, I just added a condition that must be
met before the module is called - don't think that would cause the Solver
code to all of a sudden not work...

Below is the now "not working" code as well as the working code for two
different instances where I'm using Solver. Any feedback is appreciated as
to why the "not-working" code is not working (**** precedes the line that is
not working under "Code "not working"" and then the updated code under "Code
that works" -- notice how in the first instance, the code that works is very
similar to the code that does NOT work in the second instance).


Code "not working" (first instance using Solver):

Sheets("Transaction Summary").Activate
'Set the binary range to be used in the
sumproduct calc
ActiveWorkbook.Names.Add Name:="holdclearbin", _
RefersToR1C1:=.Range("AJ2",
..Range("AI65536").End(xlUp).Offset(0, 1))
'Set the commission range for the sumproduct
calculation
ActiveWorkbook.Names.Add Name:="holdclearcomm", _
RefersToR1C1:=.Range("L2",
..Range("L65536").End(xlUp))
'Set the sumproduct calculation cell and range
.Range("AK1").Formula =
"=SUMPRODUCT(holdclearcomm,holdclearbin)"
ActiveWorkbook.Names.Add
Name:="holdclearsumprod", _
RefersToR1C1:=.Range("AK1")
'Set the sum of the commission calculation
.Range("AL1").Value = A.Offset(0, -5).Value
'Set and execute Solver
Sheets("Transaction Summary").Activate
SolverReset
Application.DisplayAlerts = False
****SolverOk SetCell:="holdclearsumprod",
MaxMinVal:=3, ValueOf:="$AL$1", ByChange:="holdclearbin"
SolverAdd CellRef:="holdclearbin", Relation:=5,
FormulaText:="binary"
SolverOptions MaxTime:=100, Iterations:=100,
Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1,
Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False,
Convergence:=0.0001, AssumeNonNeg:=False
SolverSolve userfinish:=True
Application.DisplayAlerts = True

Code that works (first instance using Solver):

Sheets("Transaction Summary").Activate
'Set the binary range to be used in the
sumproduct calc
ActiveWorkbook.Names.Add Name:="holdclearbin", _
RefersToR1C1:=.Range("AJ2",
..Range("AI65536").End(xlUp).Offset(0, 1))
'Set the commission range for the sumproduct
calculation
ActiveWorkbook.Names.Add Name:="holdclearcomm", _
RefersToR1C1:=.Range("L2",
..Range("L65536").End(xlUp))
'Set the sumproduct calculation cell and range
.Range("AK1").Formula =
"=SUMPRODUCT(holdclearcomm,holdclearbin)"
ActiveWorkbook.Names.Add
Name:="holdclearsumprod", _
RefersToR1C1:=.Range("AK1")
'Set the sum of the commission calculation
.Range("AL1").Value = A.Offset(0, -5).Value
'Set and execute Solver
Sheets("Transaction Summary").Activate
SolverReset
Application.DisplayAlerts = False
****SolverOk SetCell:="holdclearsumprod",
MaxMinVal:=3, ValueOf:=Range("AL1").Value, ByChange:="holdclearbin"
SolverAdd CellRef:="holdclearbin", Relation:=5,
FormulaText:="binary"
SolverOptions MaxTime:=100, Iterations:=100,
Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1,
Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False,
Convergence:=0.0001, AssumeNonNeg:=False
SolverSolve userfinish:=True
Application.DisplayAlerts = True



Code "not working" (second instance using Solver):

Sheets("Transaction
Summary").Activate
.Range("L1").ClearContents
'Set commission range
ActiveWorkbook.Names.Add
Name:="payfclearcomm", _

RefersToR1C1:=.Range(.Range("L1").End(xlDown), .Range("L65536").End(xlUp))
'Set binary range
ActiveWorkbook.Names.Add
Name:="payfclearbin", _

RefersToR1C1:=.Range(.Range("AA1").End(xlDown).Off set(0, 1),
..Range("AA65536").End(xlUp).Offset(0, 1))
'Set month weight range

..Range("AA1").End(xlDown).Offset(0, 2).Value = 1
With
..Range(.Range("AA1").End(xlDown).Offset(1, 2),
..Range("AA65536").End(xlUp).Offset(0, 2))
.Formula = _

"=IF(MONTH(RC[-16])<MONTH(R[-1]C[-16]),1+R[-1]C,R[-1]C)"
.Formula = .Value
End With
ActiveWorkbook.Names.Add
Name:="payfclearmonth", _

RefersToR1C1:=.Range(.Range("AA1").End(xlDown).Off set(0, 2),
..Range("AA65536").End(xlUp).Offset(0, 2))
'Set sumif for Forward
Balance total
.Range("AD1").Formula = _

"=SUMIF(Linkpayholdclear!C[-23],Linkpayhold!R[1]C[-18],Linkpayholdclear!C[-28])"
'Set format of cell AD1
.Range("AD1").NumberFormat =
"0.00000"
'Set commission sumproduct
.Range("AE1").Formula =
"=SUMPRODUCT(payfclearcomm,payfclearbin)"
'Set month weight sumproduct
.Range("AF1").Formula =
"=SUMPRODUCT(payfclearbin,payfclearmonth)"
'Solver execution
SolverReset
Application.DisplayAlerts = False
SolverOk SetCell:="$AF$1",
MaxMinVal:=2, ValueOf:="0", ByChange:="payfclearbin"
SolverAdd
CellRef:="payfclearbin", Relation:=5, FormulaText:="binary"
****SolverAdd CellRef:="$AE$1",
Relation:=2, FormulaText:=Range("AD1").Value
SolverOptions MaxTime:=100,
Iterations:=100, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False,
Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False,
Convergence:=0.0001, AssumeNonNeg:=False
SolverSolve userfinish:=True
Application.DisplayAlerts = True


Code that works (second instance using Solver):

Sheets("Transaction Summary").Activate
.Range("L1").ClearContents
'Set commission range
ActiveWorkbook.Names.Add
Name:="payfclearcomm", _

RefersToR1C1:=.Range(.Range("L1").End(xlDown), .Range("L65536").End(xlUp))
'Set binary range
ActiveWorkbook.Names.Add
Name:="payfclearbin", _

RefersToR1C1:=.Range(.Range("AA1").End(xlDown).Off set(0, 1),
..Range("AA65536").End(xlUp).Offset(0, 1))
'Set month weight range

..Range("AA1").End(xlDown).Offset(0, 2).Value = 1
With
..Range(.Range("AA1").End(xlDown).Offset(1, 2),
..Range("AA65536").End(xlUp).Offset(0, 2))
.Formula = _

"=IF(MONTH(RC[-16])<MONTH(R[-1]C[-16]),1+R[-1]C,R[-1]C)"
.Formula = .Value
End With
ActiveWorkbook.Names.Add
Name:="payfclearmonth", _

RefersToR1C1:=.Range(.Range("AA1").End(xlDown).Off set(0, 2),
..Range("AA65536").End(xlUp).Offset(0, 2))
'Set sumif for Forward
Balance total
.Range("AD1").Formula = _

"=SUMIF(Linkpayholdclear!C[-23],Linkpayhold!R[1]C[-18],Linkpayholdclear!C[-28])"
'Set format of cell AD1
.Range("AD1").NumberFormat =
"0.00000"
'Set commission sumproduct
.Range("AE1").Formula =
"=SUMPRODUCT(payfclearcomm,payfclearbin)"
'Set month weight sumproduct
.Range("AF1").Formula =
"=SUMPRODUCT(payfclearbin,payfclearmonth)"
'Solver execution
Sheets("Transaction
Summary").Activate
SolverReset
Application.DisplayAlerts = False
SolverOk SetCell:="$AF$1",
MaxMinVal:=2, ValueOf:="0", ByChange:="payfclearbin"
SolverAdd
CellRef:="payfclearbin", Relation:=5, FormulaText:="binary"
****SolverAdd CellRef:="$AE$1",
Relation:=2, FormulaText:="$AD$1"
SolverOptions MaxTime:=100,
Iterations:=100, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False,
Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False,
Convergence:=0.0001, AssumeNonNeg:=False
SolverSolve userfinish:=True
Application.DisplayAlerts = True


--
Robert

Hi Dana,

Thanks for the information regarding the number of cells that can change (I
didn't know Solver had a limit of 200).

The question that is still unanswered is why would the code directly below
not work, while the code below that works? The change, you'll notice is with
the "ValueOf" parameter - it just doesn't make sense as to why the code
directly below does not work:

***'Code below does not work
SolverOk _
SetCell:="holdclearsumprod", _
MaxMinVal:=3, _
ValueOf:="$AL$1", _
ByChange:="holdclearbin"

***'Code below works
SolverOk _
SetCell:="holdclearsumprod", _
MaxMinVal:=3, _
ValueOf:=Range("AL1").Value, _
ByChange:="holdclearbin"

--
Robert


"Dana DeLouis" wrote:

to why the "not-working" code is not working (**** precedes the line
that is
not working under "Code "not working""

Hi. I can't run your code, so this thought would only be a guess...

ActiveWorkbook.Names.Add Name:="holdclearbin", _
RefersToR1C1:=.Range("AJ2",
.Range("AI65536").End(xlUp).Offset(0, 1))

This code is setting a range of "Binary" changing cells.
We do not know how many cells are set in this code.
However, Solver is limited to a total of 200 changing cells.
You may want to include code to check for this.

Here are some additional ideas.

****SolverOk SetCell:="holdclearsumprod", MaxMinVal:=3, ...etc

It might be better for posting in newsgroups code that is a little easier
to read.
I think it might be better to use Remarks in your code so that when you
copy, it makes it a little easier for you.

for example.

'// **** The next line does not load into Solver
SolverOk _
SetCell:="holdclearsumprod", _
MaxMinVal:=3, _
ValueOf:="$AL$1", _
ByChange:="holdclearbin"


Let me pause here and note that the Target is a Cell Range, and not a
constant value.
At this point, we do not know if cell AL1 changes as the the Solver model
changes.
This will not work well. It's usually better to set it to a value.

If you copy you code, it would take a long time to clean it up the line
breaks.
See if using the line continuation character makes it easier to read for
you.

SolverOptions _
MaxTime:=100, _
Iterations:=100, _
Precision:=0.000001, _
AssumeLinear:=False, _
StepThru:=False, _
Estimates:=1, _
derivatives:=1, _
SearchOption:=1, _
IntTolerance:=5, _
Scaling:=False, _
Convergence:=0.0001, _
AssumeNonNeg:=False

--
HTH :)
Dana DeLouis


"robs3131" wrote in message
...
Hi,

I'm having some issues with Solver -- while I have found workarounds to
resolve the issue, I have no idea as to why the code needed to be
changed and
would like to know if anyone has any ideas. What is really driving me
crazy
is that the original code was working fine until a couple of days ago --
I
didn't change any code in the module, I just added a condition that must
be
met before the module is called - don't think that would cause the
Solver
code to all of a sudden not work...

Below is the now "not working" code as well as the working code for two
different instances where I'm using Solver. Any feedback is appreciated
as
to why the "not-working" code is not working (**** precedes the line
that is
not working under "Code "not working"" and then the updated code under
"Code
that works" -- notice how in the first instance, the code that works is
very
similar to the code that does NOT work in the second instance).


Code "not working" (first instance using Solver):

Sheets("Transaction Summary").Activate
'Set the binary range to be used in the
sumproduct calc
ActiveWorkbook.Names.Add
Name:="holdclearbin", _
RefersToR1C1:=.Range("AJ2",
.Range("AI65536").End(xlUp).Offset(0, 1))
'Set the commission range for the
sumproduct
calculation
ActiveWorkbook.Names.Add
Name:="holdclearcomm", _
RefersToR1C1:=.Range("L2",
.Range("L65536").End(xlUp))
'Set the sumproduct calculation cell and
range
.Range("AK1").Formula =
"=SUMPRODUCT(holdclearcomm,holdclearbin)"
ActiveWorkbook.Names.Add
Name:="holdclearsumprod", _
RefersToR1C1:=.Range("AK1")
'Set the sum of the commission
calculation
.Range("AL1").Value = A.Offset(0, -5).Value
'Set and execute Solver
Sheets("Transaction Summary").Activate
SolverReset
Application.DisplayAlerts = False
****SolverOk SetCell:="holdclearsumprod",
MaxMinVal:=3, ValueOf:="$AL$1", ByChange:="holdclearbin"
SolverAdd CellRef:="holdclearbin",
Relation:=5,
FormulaText:="binary"
SolverOptions MaxTime:=100, Iterations:=100,
Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1,
Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False,
Convergence:=0.0001, AssumeNonNeg:=False
SolverSolve userfinish:=True
Application.DisplayAlerts = True

Code that works (first instance using Solver):

Sheets("Transaction Summary").Activate
'Set the binary range to be used in the
sumproduct calc
ActiveWorkbook.Names.Add
Name:="holdclearbin", _
RefersToR1C1:=.Range("AJ2",
.Range("AI65536").End(xlUp).Offset(0, 1))
'Set the commission range for the
sumproduct
calculation
ActiveWorkbook.Names.Add
Name:="holdclearcomm", _
RefersToR1C1:=.Range("L2",
.Range("L65536").End(xlUp))
'Set the sumproduct calculation cell and
range
.Range("AK1").Formula =
"=SUMPRODUCT(holdclearcomm,holdclearbin)"
ActiveWorkbook.Names.Add
Name:="holdclearsumprod", _
RefersToR1C1:=.Range("AK1")
'Set the sum of the commission
calculation
.Range("AL1").Value = A.Offset(0, -5).Value
'Set and execute Solver
Sheets("Transaction Summary").Activate
SolverReset
Application.DisplayAlerts = False
****SolverOk SetCell:="holdclearsumprod",
MaxMinVal:=3, ValueOf:=Range("AL1").Value, ByChange:="holdclearbin"
SolverAdd CellRef:="holdclearbin",
Relation:=5,
FormulaText:="binary"
SolverOptions MaxTime:=100, Iterations:=100,
Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1,
Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False,
Convergence:=0.0001, AssumeNonNeg:=False
SolverSolve userfinish:=True
Application.DisplayAlerts = True



Code "not working" (second instance using Solver):

Sheets("Transaction
Summary").Activate
.Range("L1").ClearContents
'Set commission range
ActiveWorkbook.Names.Add
Name:="payfclearcomm", _

RefersToR1C1:=.Range(.Range("L1").End(xlDown),
.Range("L65536").End(xlUp))
'Set binary range
ActiveWorkbook.Names.Add
Name:="payfclearbin", _

RefersToR1C1:=.Range(.Range("AA1").End(xlDown).Off set(0, 1),
.Range("AA65536").End(xlUp).Offset(0, 1))
'Set month weight range

.Range("AA1").End(xlDown).Offset(0, 2).Value = 1
With
.Range(.Range("AA1").End(xlDown).Offset(1, 2),
.Range("AA65536").End(xlUp).Offset(0, 2))
.Formula = _

"=IF(MONTH(RC[-16])<MONTH(R[-1]C[-16]),1+R[-1]C,R[-1]C)"
.Formula = .Value
End With
ActiveWorkbook.Names.Add
Name:="payfclearmonth", _

RefersToR1C1:=.Range(.Range("AA1").End(xlDown).Off set(0, 2),
.Range("AA65536").End(xlUp).Offset(0, 2))
'Set sumif for Forward
Balance total
.Range("AD1").Formula = _


"=SUMIF(Linkpayholdclear!C[-23],Linkpayhold!R[1]C[-18],Linkpayholdclear!C[
-28])"
'Set format of cell
AD1
.Range("AD1").NumberFormat =
"0.00000"
'Set commission
sumproduct
.Range("AE1").Formula =
"=SUMPRODUCT(payfclearcomm,payfclearbin)"
'Set month weight
sumproduct
.Range("AF1").Formula =
"=SUMPRODUCT(payfclearbin,payfclearmonth)"
'Solver execution
SolverReset
Application.DisplayAlerts =
False
SolverOk SetCell:="$AF$1",
MaxMinVal:=2, ValueOf:="0", ByChange:="payfclearbin"
SolverAdd
CellRef:="payfclearbin", Relation:=5, FormulaText:="binary"
****SolverAdd
CellRef:="$AE$1",
Relation:=2, FormulaText:=Range("AD1").Value
SolverOptions MaxTime:=100,
Iterations:=100, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False,
Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5,
Scaling:=False,
Convergence:=0.0001, AssumeNonNeg:=False
SolverSolve userfinish:=True
Application.DisplayAlerts =
True


Code that works (second instance using Solver):

Sheets("Transaction Summary").Activate
.Range("L1").ClearContents
'Set commission range
ActiveWorkbook.Names.Add
Name:="payfclearcomm", _

RefersToR1C1:=.Range(.Range("L1").End(xlDown),
.Range("L65536").End(xlUp))
'Set binary range
ActiveWorkbook.Names.Add
Name:="payfclearbin", _

RefersToR1C1:=.Range(.Range("AA1").End(xlDown).Off set(0, 1),
.Range("AA65536").End(xlUp).Offset(0, 1))
'Set month weight range

.Range("AA1").End(xlDown).Offset(0, 2).Value = 1
With
.Range(.Range("AA1").End(xlDown).Offset(1, 2),
.Range("AA65536").End(xlUp).Offset(0, 2))
.Formula = _

"=IF(MONTH(RC[-16])<MONTH(R[-1]C[-16]),1+R[-1]C,R[-1]C)"
.Formula = .Value
End With
ActiveWorkbook.Names.Add
Name:="payfclearmonth", _

RefersToR1C1:=.Range(.Range("AA1").End(xlDown).Off set(0, 2),
.Range("AA65536").End(xlUp).Offset(0, 2))
'Set sumif for Forward
Balance total
.Range("AD1").Formula = _


"=SUMIF(Linkpayholdclear!C[-23],Linkpayhold!R[1]C[-18],Linkpayholdclear!C[
-28])"
'Set format of cell
AD1
.Range("AD1").NumberFormat =

Cell AL1 is a constant -- it does not change as the results change. You'll
notice that in both examples from my most recent post, cell AL1 is the what
"Valueof" is set to -- in one case it works, in the other it
doesn't...doesn't make sense as to why this would be.

What is the "trace precedence" key? I looked in Solver and then googled it
but didn't find anything.

Thanks for your help,

--
Robert


"Dana DeLouis" wrote:


"robs3131" wrote in message
...
Hi Dana,

Thanks for the information regarding the number of cells that can change
(I
didn't know Solver had a limit of 200).

The question that is still unanswered is why would the code directly
below
not work, while the code below that works? The change, you'll notice is
with
the "ValueOf" parameter - it just doesn't make sense as to why the code
directly below does not work:

***'Code below does not work
SolverOk _
SetCell:="holdclearsumprod", _
MaxMinVal:=3, _
ValueOf:="$AL$1", _
ByChange:="holdclearbin"

***'Code below works
SolverOk _
SetCell:="holdclearsumprod", _
MaxMinVal:=3, _
ValueOf:=Range("AL1").Value, _
ByChange:="holdclearbin"

--
Robert

Hi. I actually didn't notice that, but I did make a note about that by
chance when I mentioned ...

Let me pause here and note that the Target is a Cell Range, and not a
constant value.
At this point, we do not know if cell AL1 changes as the Solver model
changes.
This will not work well. It's usually better to set it to a value.

So, I guess the question is...does AL1 change as the changing cells
change?
One technique is to select your target cell and keep clicking the "Trace
Precedence" key to make sure the cell is not being changed by solver.

If Solver sets the changing cells to the solution, and this changes the
solution (Target Cell), we end up in a loop, and Solver gets confused.
Solver will give up very quickly if it senses anything that will get it
confused. That is why it is usually best to set it to a Value as you have
done in the code that works.
If AL1 does change, then you need to re-think you model.

--
HTH :)
Dana DeLouis








"Dana DeLouis" wrote:

to why the "not-working" code is not working (**** precedes the line
that is
not working under "Code "not working""

Hi. I can't run your code, so this thought would only be a guess...

ActiveWorkbook.Names.Add Name:="holdclearbin", _
RefersToR1C1:=.Range("AJ2",
.Range("AI65536").End(xlUp).Offset(0, 1))

This code is setting a range of "Binary" changing cells.
We do not know how many cells are set in this code.
However, Solver is limited to a total of 200 changing cells.
You may want to include code to check for this.

Here are some additional ideas.

****SolverOk SetCell:="holdclearsumprod", MaxMinVal:=3,
...etc

It might be better for posting in newsgroups code that is a little
easier
to read.
I think it might be better to use Remarks in your code so that when you
copy, it makes it a little easier for you.

for example.

'// **** The next line does not load into Solver
SolverOk _
SetCell:="holdclearsumprod", _
MaxMinVal:=3, _
ValueOf:="$AL$1", _
ByChange:="holdclearbin"


Let me pause here and note that the Target is a Cell Range, and not a
constant value.
At this point, we do not know if cell AL1 changes as the the Solver
model
changes.
This will not work well. It's usually better to set it to a value.

If you copy you code, it would take a long time to clean it up the line
breaks.
See if using the line continuation character makes it easier to read
for
you.

SolverOptions _
MaxTime:=100, _
Iterations:=100, _
Precision:=0.000001, _
AssumeLinear:=False, _
StepThru:=False, _
Estimates:=1, _
derivatives:=1, _
SearchOption:=1, _
IntTolerance:=5, _
Scaling:=False, _
Convergence:=0.0001, _
AssumeNonNeg:=False

--
HTH :)
Dana DeLouis


"robs3131" wrote in message
...
Hi,

I'm having some issues with Solver -- while I have found workarounds
to
resolve the issue, I have no idea as to why the code needed to be
changed and
would like to know if anyone has any ideas. What is really driving
me
crazy
is that the original code was working fine until a couple of days
ago --
I
didn't change any code in the module, I just added a condition that
must
be
met before the module is called - don't think that would cause the
Solver
code to all of a sudden not work...

Below is the now "not working" code as well as the working code for
two
different instances where I'm using Solver. Any feedback is
appreciated
as
to why the "not-working" code is not working (**** precedes the line
that is
not working under "Code "not working"" and then the updated code
under
"Code
that works" -- notice how in the first instance, the code that works
is
very
similar to the code that does NOT work in the second instance).


Code "not working" (first instance using Solver):

Sheets("Transaction Summary").Activate
'Set the binary range to be used in
the
sumproduct calc
ActiveWorkbook.Names.Add
Name:="holdclearbin", _
RefersToR1C1:=.Range("AJ2",
.Range("AI65536").End(xlUp).Offset(0, 1))
'Set the commission range for the
sumproduct
calculation
ActiveWorkbook.Names.Add
Name:="holdclearcomm", _
RefersToR1C1:=.Range("L2",
.Range("L65536").End(xlUp))
'Set the sumproduct calculation cell
and
range
.Range("AK1").Formula =
"=SUMPRODUCT(holdclearcomm,holdclearbin)"
ActiveWorkbook.Names.Add
Name:="holdclearsumprod", _
RefersToR1C1:=.Range("AK1")
'Set the sum of the commission
calculation
.Range("AL1").Value =
A.Offset(0, -5).Value
'Set and execute Solver
Sheets("Transaction Summary").Activate
SolverReset
Application.DisplayAlerts = False
****SolverOk SetCell:="holdclearsumprod",
MaxMinVal:=3, ValueOf:="$AL$1", ByChange:="holdclearbin"
SolverAdd CellRef:="holdclearbin",
Relation:=5,
FormulaText:="binary"
SolverOptions MaxTime:=100,
Iterations:=100,
Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1,
Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False,
Convergence:=0.0001, AssumeNonNeg:=False
SolverSolve userfinish:=True
Application.DisplayAlerts = True

Code that works (first instance using Solver):

Sheets("Transaction Summary").Activate
'Set the binary range to be used in
the
sumproduct calc
ActiveWorkbook.Names.Add
Name:="holdclearbin", _
RefersToR1C1:=.Range("AJ2",
.Range("AI65536").End(xlUp).Offset(0, 1))
'Set the commission range for the
sumproduct
calculation
ActiveWorkbook.Names.Add
Name:="holdclearcomm", _
RefersToR1C1:=.Range("L2",
.Range("L65536").End(xlUp))
'Set the sumproduct calculation cell
and
range
.Range("AK1").Formula =
"=SUMPRODUCT(holdclearcomm,holdclearbin)"
ActiveWorkbook.Names.Add
Name:="holdclearsumprod", _
RefersToR1C1:=.Range("AK1")
'Set the sum of the commission
calculation
.Range("AL1").Value =
A.Offset(0, -5).Value
'Set and execute Solver
Sheets("Transaction Summary").Activate
SolverReset
Application.DisplayAlerts = False
****SolverOk SetCell:="holdclearsumprod",
MaxMinVal:=3, ValueOf:=Range("AL1").Value, ByChange:="holdclearbin"
SolverAdd CellRef:="holdclearbin",
Relation:=5,
FormulaText:="binary"
SolverOptions MaxTime:=100,
Iterations:=100,
Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1,
Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False,
Convergence:=0.0001, AssumeNonNeg:=False
SolverSolve userfinish:=True
Application.DisplayAlerts = True



Code "not working" (second instance using Solver):

Sheets("Transaction
Summary").Activate
.Range("L1").ClearContents
'Set commission range
ActiveWorkbook.Names.Add
Name:="payfclearcomm", _

RefersToR1C1:=.Range(.Range("L1").End(xlDown),
.Range("L65536").End(xlUp))
'Set binary range
ActiveWorkbook.Names.Add
Name:="payfclearbin", _

RefersToR1C1:=.Range(.Range("AA1").End(xlDown).Off set(0, 1),
.Range("AA65536").End(xlUp).Offset(0, 1))
'Set month weight
range

.Range("AA1").End(xlDown).Offset(0, 2).Value = 1
With
.Range(.Range("AA1").End(xlDown).Offset(1, 2),
.Range("AA65536").End(xlUp).Offset(0, 2))
.Formula = _

"=IF(MONTH(RC[-16])<MONTH(R[-1]C[-16]),1+R[-1]C,R[-1]C)"
.Formula = .Value
End With
ActiveWorkbook.Names.Add
Name:="payfclearmonth", _

Thanks for your help Dana.

--
Robert


"Dana DeLouis" wrote:

"Valueof" is set to -- in one case it works, in the other it
doesn't...doesn't make sense as to why this would be.

Hi. The "Value of" is expecting a number.

ValueOf:="$AL$1", _
is simply setting it to a string "$AL$1" , and solver does not see this as
a value "From AL1"

ValueOf:=Range("AL1").Value, _
This takes the value in AL1, and places it into solver. This should work.

If you record a macro while doing a simple Solver example, you will notice
that you can not place a Cell Address (A stirng like A1) in the "Value
of" box. This is by design to prevent Solver from going around in
circles. You need to place a value here.

What is the "trace precedence" key? I looked in Solver and then googled
it
but didn't find anything.

This is not part of Solver, but a feature of an Excel worksheet.
It is a button you can find on the formula auditing toolbar. (On the
Formula Tab in Excel 2007)
You can select any cell, and select Trace Dependent / Precedent and see
which cells are related to the selected cell in question.
(You can keep clicking the button to keep going up/down levels.)

--
HTH :)
Dana DeLouis


"robs3131" wrote in message
...
Cell AL1 is a constant -- it does not change as the results change.
You'll
notice that in both examples from my most recent post, cell AL1 is the
what
"Valueof" is set to -- in one case it works, in the other it
doesn't...doesn't make sense as to why this would be.

What is the "trace precedence" key? I looked in Solver and then googled
it
but didn't find anything.

Thanks for your help,

--
Robert