XL5: SOLVER.OK Only Uses Last Cell Range for Changing Cells

• Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, when you turn on the macro recorder, and use the Solver Add-in, if you enter a range of cells in the By Changing Cells box, the recorded macro includes only the last range of cells.

Additionally, if you use the SOLVER.OK function in either a Visual Basic module, or a version 4.0 macro, only the value of the last cell in the by_changing argument is changed when you run the procedure.

CAUSE

The SOLVER.OK function uses only the last cell in the by_changing argument. This behavior occurs when you use the Solver Add-in in a recorded macro, or when you use the SOLVER.OK function in a macro. This behavior occurs when you use both the Visual Basic language, and the version 4.0 macro language.

The following is an example of the code that is recorded if you use the Solver Add-in, and you enter cells A1 and C1 in the By Changing Cells box while recording a macro:

Visual Basic

   Application.ExecuteExcel4Macro String:= _
      "[SOLVER.XLA]SOLVER!SOLVER.OK(!R3C1,3,4,(!R1C3))"
   Application.ExecuteExcel4Macro String:= _
      "[SOLVER.XLA]SOLVER!SOLVER.SOLVE()"

Version 4.0 Macro

   =SOLVER.OK(!$A$3,3,4,(!$C$1))
   =SOLVER.SOLVE()

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel version 5.0c.

Note that this is not a problem in Microsoft Excel for the Macintosh, version 5.0.

REFERENCES

For an Overview Of Working With Microsoft Excel Solver, choose the Search button in Help and type:

   Solver

   SOLVER.OK function