VBA Excel求解器代码 - 按列循环 [英] VBA Excel Solver Code - Looping by Columns

问题描述

我正在为300个列中的数据设置一个Solver循环。这是我到目前为止：

I'm trying to set up a loop for Solver for data held in 300 columns. This is what I have so far:

Sub SolverProp()

    SolverReset

    SolverOk SetCell:="$B$20", MaxMinVal:=3, ValueOf:="$B$3", ByChange:="$B$28", _
        Engine:=1, EngineDesc:="GRG Nonlinear"


    SolverSolve

End Sub

此代码解决了列B中保存的数据所需的内容。它会更改B28直到B20等于B3。
我试图让它循环，所以每300个案例（设置完全一样），它将解决方程式。例如，为了解决列C，代码将如下所示：

This code solves what I need for data held in column B. It changes B28 until B20 is equal to B3. I'm trying to make it loop so that for every 300 cases (set up exactly the same) it'll solve the equation. For example to solve column C the code would look like this:

Sub SolverProp()

    SolverReset

    SolverOk SetCell:="$C$20", MaxMinVal:=3, ValueOf:="$C$3", ByChange:="$C$28", _
        Engine:=1, EngineDesc:="GRG Nonlinear"


    SolverSolve

End Sub

推荐答案

您可以使用Range对象的Address属性循环遍历所有300列。

You can use the Address property of the Range object to loop through all 300 columns.

首先，您需要3个变量来保存SetCell，ValueOf和ByChange的位置。

First you need 3 variables to hold locations of SetCell, ValueOf, and ByChange.

Dim setCellRange as Range, valueOfRange as Range, byChangeRange as Range

Set setCellRange = ActiveSheet.Range("B20")
Set valueOfRange = ActiveSheet.Range("B3")
Set byChangeRange = ActiveSheet.Range("B28")

然后，您可以使用以下循环遍历每一列。

Then you can use the following loop to iterate through each column.

Dim i as Long

For i = 1 to 300

  SolverReset

  SolverOk SetCell:=setCellRange.Address, MaxMinVal:=3, ValueOf:=valueOfRange.Address, ByChange:=byChangeRange.Address, _
    Engine:=1, EngineDesc:="GRG Nonlinear"


  SolverSolve

  Set setCellRange = setCellRange.Cells(1, 2)
  Set valueOfRange = valueOfRange.Cells(1, 2)
  Set byChangeRange = byChangeRange.Cells(1, 2)

Next i

希望这有帮助！

这篇关于VBA Excel求解器代码 - 按列循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！