Sover -- how do you stop "The maximum time limit was reached..." b

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
 

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

Top