R
robs3131
Hi all,
I am using Solver as part of a large piece of code -- the code below is
specific to Solver. The issue I have is that the message "The maximum time
limit was reached; continue anyway?" comes up when the max time limit is
reached. I would like the code to keep going so users aren't bothered with
the menu.
Specifically, I would like to automate the selecting of the "Stop" button --
I want this to be transparent to the user so they don't even see the menu
come up. Apparently "Application.DisplayAlerts = False" doesn't do this
(makes sense since it's not clear what the user's choice would be). Any help
is appreciated!
Code:
With Sheets("Transaction Summary")
'Set $ range
ActiveWorkbook.Names.Add Name:="payfclearcomm", _
RefersTo:=.Range(.Range("L1").End(xlDown), .Range("L65536").End(xlUp))
'Set binary range
ActiveWorkbook.Names.Add Name:="payfclearbin", _
RefersTo:=.Range(.Range("AA1").End(xlDown).Offset(0, 1),
..Range("AA65536").End(xlUp).Offset(0, 1))
'Set month range
ActiveWorkbook.Names.Add Name:="payfclearmonth", _
RefersTo:=.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:=10, 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
Else
End If
End With
I am using Solver as part of a large piece of code -- the code below is
specific to Solver. The issue I have is that the message "The maximum time
limit was reached; continue anyway?" comes up when the max time limit is
reached. I would like the code to keep going so users aren't bothered with
the menu.
Specifically, I would like to automate the selecting of the "Stop" button --
I want this to be transparent to the user so they don't even see the menu
come up. Apparently "Application.DisplayAlerts = False" doesn't do this
(makes sense since it's not clear what the user's choice would be). Any help
is appreciated!
Code:
With Sheets("Transaction Summary")
'Set $ range
ActiveWorkbook.Names.Add Name:="payfclearcomm", _
RefersTo:=.Range(.Range("L1").End(xlDown), .Range("L65536").End(xlUp))
'Set binary range
ActiveWorkbook.Names.Add Name:="payfclearbin", _
RefersTo:=.Range(.Range("AA1").End(xlDown).Offset(0, 1),
..Range("AA65536").End(xlUp).Offset(0, 1))
'Set month range
ActiveWorkbook.Names.Add Name:="payfclearmonth", _
RefersTo:=.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:=10, 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
Else
End If
End With