vba EXCEL 求解器自动化
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19293059/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
EXCEL Solver automation
提问by DavidS
I'am using the Excel Solver to minimize a cost based on 4 variables.
我正在使用 Excel 求解器来最小化基于 4 个变量的成本。
The point is that solver can solve one target cell at a time but I need to do that on 250 items for multiple suppliers. I tried to sum up all the price but the solver is limited to 200 variables and it takes forever to perform it.
关键是求解器一次可以求解一个目标单元格，但我需要对多个供应商的 250 个项目执行此操作。我试图总结所有价格，但求解器仅限于 200 个变量，并且需要永远执行它。
My question is: How can I automate the solver using a macro?
我的问题是：如何使用宏自动化求解器？
My data are organized as follows:
我的数据组织如下：
Prices: Column A to D Constraint on Qty: Column E Quantities: Column F to I Demand (depending on Quantities): Column J Total Price (to minimize): Column K
价格：A 栏到 D 栏数量限制：E 栏数量：F 栏到 I 需求（取决于数量）：J 栏总价（最小化）：K 栏
I tried get the following macro code for 1 item:
我尝试为 1 个项目获取以下宏代码：
SolverOk SetCell:="$K", MaxMinVal:=2, ValueOf:=0, ByChange:="$F:$I", _
Engine:=2, EngineDesc:="Simplex LP"
SolverAdd CellRef:="$F:$I", Relation:=4, FormulaText:="integer"
SolverOk
SolverAdd CellRef:="$F:$I", Relation:=3, FormulaText:="0"
SolverOk
SolverAdd CellRef:="$J", Relation:=3, FormulaText:="$E"
SolverOk
SolverSolve
SolverOk
SolverDelete CellRef:="$F:$I", Relation:=4
SolverDelete CellRef:="$F:$I", Relation:=3, FormulaText:="0"
SolverDelete CellRef:="$J", Relation:=3, FormulaText:="$E"
End Sub
I need your help to automate this code from line 7 to 257.
我需要你的帮助来自动化从第 7 行到 257 行的这段代码。
Thanks
谢谢
David
大卫
回答by Rrgg
I think the question you are really asking is how to create a LOOP in vba. for beginers, "for loops" are easiest. start your code with:
我认为您真正要问的问题是如何在 vba 中创建 LOOP。对于初学者来说，“for 循环”是最简单的。开始你的代码：
for i = 7 to 257
put your code here that you want to repeat... the thing you want to change, refer to "i"
把你想重复的代码放在这里……你想改变的东西，参考“i”
This line for example would not be refering to the 13th row as it is here: SolverAdd CellRef:="$F$13:$I$13", Relation:=4, FormulaText:="integer"
例如，这一行不会引用第 13 行，因为它在这里： SolverAdd CellRef:="$F$13:$I$13", Relation:=4, FormulaText:="integer"
but rather referring to the ith row as shown here
而是指这里显示的第 i 行
SolverAdd CellRef:="$F$" & i & ":$I$" & i, Relation:=4, FormulaText:="integer"
and so on. Do that throughout for the thing you want to change. and end your loop with this:
等等。对于你想要改变的事情，从头到尾都这样做。并以此结束循环：
Next
let me know if this helped.
如果这有帮助，请告诉我。
回答by Vik
For row = 1 To 10
For col = 1 To 7
SolverReset
SolverOptions Precision:=0.001
SolverOK SetCell:="INDEX(ObjFn," & row & "," & col & ")", _
MaxMinVal:=3, _
ValueOf:=0, _
ByChange:="INDEX(ByChanging," & row & "," & col & ")"
solverResult = Application.Run("SolverSolve", True)
Next col
Next row
Here ObjFn is a named range on excel and ByChanging is another named range.
这里 ObjFn 是 excel 上的命名范围，而 ByChanging 是另一个命名范围。
