Solver Problems

[ADE2]

Hi

I have a sheet called AUTOCALC which performs end of day calculations,i
copied the whole sheet and pasted it into a new sheet called INTRADAY
to do,yes you guessed it,INTRADAY calculations.

I have renamed all the sheet references in a new macro from AUTOCALC to
INTRADAY and the macro runs ok until it gets to the Solver part.I then
get the error message"Solver:An unexpected internal error occurred,or
available memory was exhausted".The original macro runs fine and there
is plenty of memory available.

Below are the original Solver model settings in AUTOCALC:

1.4455
1.0000
FALSE
100.0000

the formula in the false cell is =$B$87='AUTO CALC'!$F$87

so i thought that maybe once i changed AUTO CALC to INTRADAY it would
solve it,
=$B$21=INTRADAY!$F$21

no matter what i do the the apostrophes that surround the word AUTO
CALC will not stay in the formula when i change the name to INTRADAY


I don't seem to be able to record a new Solver macro either on the
INTRADAY sheet as i get the error message that i referred to earlier
Has anybody got any ideas as to what i am doing wrong?


Below is some of the code that contains the Solver section




Sheets("INTRADAY").Select
Range("T1:U1").Select
ActiveSheet.Paste
Range("AJ1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Sheets("INTRADAY").Select
SolverReset
SolverLoad LoadArea:="$F$14:$F$17"
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
SolverOk SetCell:="$F$21", MaxMinVal:=1, ValueOf:="0",
ByChange:="$B$21"
SolverSolve UserFinish:=True
Range("AK1").Select
Sheets("INTRADAY").Select
SolverReset
SolverLoad LoadArea:="$F$46:$F$49"
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
SolverOk SetCell:="$F$54", MaxMinVal:=1, ValueOf:="0",
ByChange:="$B$54"
SolverSolve UserFinish:=True
Sheets("INTRADAY").Select
SolverReset
SolverLoad LoadArea:="$F$81:$F$84"
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
SolverOk SetCell:="$F$87", MaxMinVal:=1, ValueOf:="0",
ByChange:="$B$87"
SolverSolve UserFinish:=True
Sheets("INTRADAY").Select
SolverReset
SolverLoad LoadArea:="$F$114:$F$117"
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
SolverOk SetCell:="$F$120", MaxMinVal:=1, ValueOf:="0",
ByChange:="$B$120"
SolverSolve UserFinish:=True
Sheets("INTRADAY").Select
Range("AJ4:AN44").Select
Selection.ClearContents
Selection.Interior.ColorIndex = xlNone
Range("AW3:AX87").Select
Selection.ClearContents
Selection.Interior.ColorIndex = xlNone
Range("AP29").Select
Range("AA1").Select
ActiveWindow.SmallScroll Down:=-9
Range("AT5:AU86").Select
Selection.Copy
Range("AW5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("AT5:AU86").Select
Selection.Copy

Thanks for your help

 

[Dana DeLouis]

I have a sheet called AUTOCALC ...

the formula in the false cell is =$B$87='AUTO CALC'!$F$87

I don't have an answer to the problem, but I think the use of ' in the
sheet name reference are there because there is a space in the sheet name.
Did you record the macro on a sheet named "'AUTO CALC'" and than later
change it to "AUTOCALC" ?

When one copies a Solver sheet with "Save Model" data, I think there is a
potential for reference problems. Sounds like your Saved Model areas that
you later load have references to other sheets. I am pretty sure Solver
will not work with references to other sheets. It doesn't keep track of
specific sheet references. It assumes all references are to the current
sheet.

I don't understand the part of your code that "Loads" a model, but then is
changed by the macro. Sounds to me that you can just skip the loading of
the model. However, you probably have constraints, and these constraints
may have bad references (to other sheets). Also, the last line of the Load
Area will have your Option preferences, so I don't think there is any need
to reset them. I don't know.

If you do wish to reset them to something different, may I suggest you
factor out the Solver options? Since your options are all the same, here
is just one idea. Note that you do not need to include a "value of = 0"
during a "Maximize" problem. Again, I am not sure of the purpose of
SolverLoad, since the model is then changed.


SolverReset
SolverLoad LoadArea:="$F$14:$F$17"
MySolverOptions
SolverOk SetCell:="$F$21", MaxMinVal:=1, ByChange:="$B$21"
SolverSolve UserFinish:=True

SolverReset
SolverLoad LoadArea:="$F$81:$F$84"
MySolverOptions
SolverOk SetCell:="$F$87", MaxMinVal:=1, ByChange:="$B$87"
SolverSolve UserFinish:=True

With a separate Sub to handle the options...

Sub MySolverOptions()
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
End Sub


For trouble shooting on your new sheet, you may want to consider manually
pulling up the solver dialog.
Reset the page, and then reload each Solver Saved model. Check it out to
make sure reference are correct, and then Save the model to a new area.
For now, try to use a sheet name with no spaces. For example, use INTRADAY,
and not INTRA DAY.

Good luck.
Dana DeLouis.