Issue with Solver

R

robs3131

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).Offset(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).Offset(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).Offset(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).Offset(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
 
R

robs3131

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 said:
to why the "not-working" code is not working (**** precedes the line that is
not working under "Code "not working""
Click to expand...

Hi. I can't run your code, so this thought would only be a guess...
ActiveWorkbook.Names.Add Name:="holdclearbin", _
Click to expand...
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
Click to expand...

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 said:
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).Offset(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).Offset(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).Offset(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).Offset(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 =
Click to expand...
Click to expand...
 
R

robs3131

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 said:
robs3131 said:
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"
Click to expand...

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

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 said:
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


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).Offset(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", _
Click to expand...
Click to expand...
Click to expand...
 
R

robs3131

Thanks for your help Dana.

--
Robert


Dana DeLouis said:
Hi. The "Value of" is expecting a number.

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

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.


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.)
Click to expand...
 

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

Sover -- how do you stop "The maximum time limit was reached..." b 0
Code that includes Solver taking extremely long time to execute 3
how to use Solver in Excel VBA 1
Object variable or with Block variable not set 9
Need advice on Solver loop 1
InputBox to name a range 2
If cancel list box don't enter FALSE in list 2
Code does not error but no copy either 4

Top