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

ID: Q123175

The information in this article applies to:

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()

Only the cell C1 reference is recorded (!R1C3 or !$C$1) even though you entered both A1 and C1 in the By Changing Cells box. If you add a cell reference to the by_changing arguments in either of the above examples, only the value of the last cell referenced in the by_changing argument is changed when you run the macro.

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

For more information about SOLVER.OK, choose the Search button in Microsoft Excel Macro Functions Help and type:

SOLVER.OK function

Additional query words: addins addin

Keywords :
Version : 5.00
Platform : WINDOWS
Issue type :