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