VBA忽略Excel Solver中引用单元格的约束

I am running a macro in Excel 2013 that uses Solver for each row (one account per row) for 30 rows.

我在Excel 2013中运行一个宏,它为每行使用Solver(每行一个帐户)30行。

Column B is my Account Name.

B列是我的帐户名。

Column C is "Total Cost".

C列是“总成本”。

Column E is # of Employees for Low Cost.

E列是低成本的员工数量。

Column F is # of Employees for Medium Cost.

F列是中等成本的员工数量。

Column G is # of Employees for High Cost.

G栏是高成本的员工数量。

Column H is "Low Cost per Employee".

H列是“每个员工的低成本”。

Column I is "Medium Cost per Employee".

第一栏是“每个员工的中等成本”。

Column J is "High Cost per Employee".

J列是“每个员工的高成本”。

Column K is sumproduct of (E:G, H:J).

K列是(E:G,H:J)的副产物。

Column L is differential between Column C and Column K. In Solver, this is the target cell that is set to 0 by changing what columns I, and J need to be.

列L是列C和列K之间的差异。在求解器中,这是通过更改I和J所需的列来设置为0的目标单元格。

I have two constraints:

我有两个限制:

I must be greater or equal to J.

我必须大于或等于J.

H must be greater or equal to I.

H必须大于或等于I.

*Note that H = C-sum(I,J) so it's not an independent variable.

*注意H = C-sum(I,J)所以它不是一个独立变量。

Question 1. for the first constraint: I have in my code:

问题1.第一个约束:我的代码中有:

SolverAdd CellRef:=Range("I" & RowCount), _
        Relation:=3, _
        FormulaText:=Range("J" & RowCount)

*Note that I tried googling formulaText but it seems that the ones I found only equate it to a value. I don't want a value. I want it to reference a cell. but the code I have kept getting ignored. the results would show J being greater than I. How do I solve this?

*请注意,我尝试使用googling formulaText,但似乎我发现的只是将它等同于一个值。我不想要一个价值。我希望它引用一个单元格。但是我一直被忽略的代码。结果显示J大于I.我该如何解决这个问题?

Question 2. I keep getting error when running

问题2.运行时我一直收到错误

   SolverAdd CellRef:=Range("H" & RowCount), _
            Relation:=3, _
            FormulaText:=Range("I" & RowCount)

I'm not sure why it would fail. Is it because H is referring an equation instead of a blank cell? If so, is there any way around it?

我不确定为什么它会失败。是因为H指的是方程而不是空白单元?如果是这样,有什么办法吗?

Thank you.

1 个解决方案

#1

have you got any further with this?

你还有什么进一步的吗?

this seemed to work ok for me, though I had a hardcoded value in H

虽然我在H中有一个硬编码的值,但这对我来说似乎没问题

SolverReset

SolverOk SetCell:="$L$3", MaxMinVal:=3, ValueOf:=0, ByChange:="$I$3:$J$3", _
    Engine:=1, EngineDesc:="GRG Nonlinear"

SolverAdd CellRef:="$H$3", Relation:=3, FormulaText:="$I$3"

SolverAdd CellRef:="$I$3", Relation:=3, FormulaText:="$J$3"

SolverSolve

#1