Hi everybody,

In Excel 2003 (Windows XP), I am trying to use the solver from a VBA macro
to solve a formula referring to its own result. The formula is included in a
spreadsheet table and is to be solved for every row of the table..

When running the macro, it is interrupted before completion and I get the
message: "Solver: an error has occurred, or available memory is saturated".

While trying to resolve the problem, I found out that the simple fact of
opening the input parameter msgbox of the Solver from the menu
Tool/Solver... and to exit it without having made any change solved the
problem!?! Afterwards, the macro could be used any number of time without
any problem.

It seems that the simple fact of opening the input parameter msgbox of the
Solver from the menu
Tool/Solver... activated some paramèters of the Solver
which are necessary to run my macro.

Here is the VBA code for my procedu

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Dim i As Long 'declare variable i loop
Dim x As Long 'declare variable x row
' A loop that will solve the equation for the 26 rows of my table.
i = 1
For i = 1 To 26
x = i + 6 'set row number
' Set up the parameters for the model.
' Set the target cell Ex to a maximum value by changing cell
Fx.
SolverReset
' Add constraint: Ex = Fx.
SolverAdd CellRef:=Cells(x, 5).Address, Relation:=2, _
FormulaText:=Cells(x, 6).Address
SolverOK SetCell:=Cells(x, 5).Address, MaxMinVal:=1, _
ByChange:=Cells(x, 6).Address
' Solve the model but do not display the Solver Results
dialog box.
SolverSolve UserFinish:=True
' Delete constraint: Ex = Fx.
SolverDelete CellRef:=Cells(x, 5).Address, Relation:=2, _
FormulaText:=Cells(x, 6).Address
' Finish and keep the final results.
SolverFinish KeepFinal:=1
Next i
End If
End Sub


What would I need to change to get this macro to work right? (without having
to first open the input parameter msgbox of the Solver from the menu
Tool/Solver... and to exit it).

I tried using the SolverOptions command to modify a few of the advanced
options of the solver but it did not help my case.


Thanks for your help,

Mathieu Fournier