Excel Solver-使用更改"ValueOf"进行优化输入项 [英] Excel Solver - Optimizing Using Changing "ValueOf" Inputs

问题描述

我正在尝试建立一个宏，该宏可以在给定2个输入的情况下对方程式进行参数化.我无法找出一种方法使规划求解使用单元格的值作为值".我现在得到的宏如下:

I'm trying to set up a macro that can parameterize an equation given 2 inputs. I cannot figure out a way to have Solver use the value of a cell as the "Value Of" though. The macro I've got at the moment is as follows:

Sub Macro2()

   SolverReset
   SolverAdd CellRef:="$G$2", Relation:=1, FormulaText:="100"
   SolverAdd CellRef:="$G$2", Relation:=3, FormulaText:="0"
   SolverOk SetCell:="$M$6", MaxMinVal:=3, ValueOf:="$B$2", ByChange:="$G$2", Engine:= _
       3, EngineDesc:="Evolutionary"
   SolverSolve

End Sub

执行该宏后，将弹出求解器结果"框，并显示Error in model. Please verify that all cells and Constraints are valid.

When the macro is executed, the Solver Results box pops up saying Error in model. Please verify that all cells and Constraints are valid.

有什么办法可以实现我想要的?

Is there any way to accomplish what I'd like to?

推荐答案

自然.

使用被引用的单元格的变量"代替直接为"ValueOf"参数引用单元格.例如:

Instead of directly referencing a cell for the "ValueOf" parameter, use a variable of the referenced cell. For example:

Sub Macro2()

    Dim target As Double
    target = Range("B2")

    SolverReset
    SolverAdd CellRef:="$G$2", Relation:=1, FormulaText:="100"
    SolverAdd CellRef:="$G$2", Relation:=3, FormulaText:="0"
    SolverOk SetCell:="$M$6", MaxMinVal:=3, ValueOf:=target, ByChange:="$G$2", _
      Engine:= 3, EngineDesc:="Evolutionary"
    SolverSolve

End Sub

希望能解决您的问题.

关于， 萨尔

这篇关于Excel Solver-使用更改"ValueOf"进行优化输入项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！