时间戳

首页 > 解决方案 > 多行的循环求解器

excel - 多行的循环求解器

问题描述

第一次到 StackOverflow，第一次使用 Excel 的 VBA 函数。

我有一个大型电子表格，我想循环求解器（使用此求解器行 523-1040），我为第一行（523）的求解器创建了一个宏，但我不知道如何得到它向下移动每一行并不断更新单元格，直到它遍历我需要的所有行。

我也希望它接受答案，保留答案并继续……我想我可能有这部分代码是正确的。

SolverOk SetCell:="$R$523", MaxMinVal:=1, ValueOf:=0, ByChange:="$D$523:$G$523" _
    , Engine:=3, EngineDesc:="Evolutionary"
SolverAdd CellRef:="$D$523", Relation:=1, FormulaText:="15"
SolverAdd CellRef:="$D$523", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$D$523", Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$E$523", Relation:=1, FormulaText:="15"
SolverAdd CellRef:="$E$523", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$E$523", Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$F$523", Relation:=1, FormulaText:="15"
SolverAdd CellRef:="$F$523", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$F$523", Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$G$523", Relation:=1, FormulaText:="79"
SolverAdd CellRef:="$G$523", Relation:=3, FormulaText:="1"
SolverAdd CellRef:="$G$523", Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$I$523", Relation:=1, FormulaText:="1500"
SolverOk SetCell:="$R$523", MaxMinVal:=1, ValueOf:=0, ByChange:="$D$523:$G$523" _
    , Engine:=3, EngineDesc:="Evolutionary"
SolverOk SetCell:="$R$523", MaxMinVal:=1, ValueOf:=0, ByChange:="$D$523:$G$523" _
    , Engine:=3, EngineDesc:="Evolutionary"
SolverSolve (True)

SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1


    The first four columns all get updated to effect the last column
    D   E   F   G   H   I       Q        R
    15  15  15  79  40  1115    317.69  1182727
    15  15  15  79  40  1699    390.06  2195098
    15  15  15  79  40  2720    491.56  4396517
    15  15  15  79  40  980     293.88  927830
    15  15  15  79  40  1653    379.15  1995816
    15  15  15  79  40  2889    494.67  4415459
    15  15  15  79  40  946     305.62  1036973
    15  15  15  79  40  1488    377.78  1976177
    15  15  15  79  40  2466    482.44  4126386
    15  15  15  79  40  437     223.64  345827
    15  15  15  79  40  450     242.50  427209
    15  15  15  79  40  1827    397.96  2315281
    15  15  15  79  40  456     219.01  335659
    15  15  15  79  40  432     232.34  387467
    15  15  15  79  40  1846    400.01  2332909
    15  15  15  79  40  680     254.58  582603
    15  15  15  79  40  1194    337.16  1365269
    15  15  15  79  40  2129    441.61  3152341
    15  15  15  79  40  734     252.43  576332

标签： excelvbaloopssolver

解决方案

你是对的，你需要一个循环。

无法对此进行测试，但您应该能够将字符串连接在一起，以便在代码的求解器部分中使用公式。基本上，您希望该行是动态的，因此您循环一组数字（523 到 1040）并将其添加到您的公式中。

Sub SolverTest()
    Dim startingRow As Long
    Dim endingrow   As Long
    Dim i           As Long
    startingRow = 523
    endingrow = 1040

    For i = startingRow To endingrow
        SolverOk SetCell:="$R$" & i, MaxMinVal:=1, ValueOf:=0, ByChange:="$D$" & i & " :$G$" & i _
        , Engine:=3, EngineDesc:="Evolutionary"
        SolverAdd CellRef:="$D$" & i, Relation:=1, FormulaText:="15"
        SolverAdd CellRef:="$D$" & i, Relation:=3, FormulaText:="0"
        SolverAdd CellRef:="$D$" & i, Relation:=4, FormulaText:="integer"
        SolverAdd CellRef:="$E$" & i, Relation:=1, FormulaText:="15"
        SolverAdd CellRef:="$E$" & i, Relation:=3, FormulaText:="0"
        SolverAdd CellRef:="$E$" & i, Relation:=4, FormulaText:="integer"
        SolverAdd CellRef:="$F$" & i, Relation:=1, FormulaText:="15"
        SolverAdd CellRef:="$F$" & i, Relation:=3, FormulaText:="0"
        SolverAdd CellRef:="$F$" & i, Relation:=4, FormulaText:="integer"
        SolverAdd CellRef:="$G$" & i, Relation:=1, FormulaText:="79"
        SolverAdd CellRef:="$G$" & i, Relation:=3, FormulaText:="1"
        SolverAdd CellRef:="$G$" & i, Relation:=4, FormulaText:="integer"
        SolverAdd CellRef:="$I$" & i, Relation:=1, FormulaText:="1500"
        SolverOk SetCell:="$R$" & i, MaxMinVal:=1, ValueOf:=0, ByChange:="$D$" & i & ":$G$" & i _
            , Engine:=3, EngineDesc:="Evolutionary"
        SolverOk SetCell:="$R$" & i, MaxMinVal:=1, ValueOf:=0, ByChange:="$D$" & i & ":$G$" & i _
            , Engine:=3, EngineDesc:="Evolutionary"
    Next
    SolverSolve (True)
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
End Sub

推荐阅读