Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with Solver
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solver Update Issue | Excel Discussion (Misc queries) | |||
Solver Update Issue | Excel Worksheet Functions | |||
excel 2000 post service pack 3 hotfix - correct an issue with solver with VBA | Excel Programming | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
Excel Solver Issue | Excel Discussion (Misc queries) |