VBA Excel Solver Code - Looping by Columns

Question

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

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

Answer 1

I've selected the above as it provides a working solution. Just to add to it, I've had to modify the model as it seems that the ValueOf property will not accept range as input, I've had to rework my module to make it return 0 when conditions are met. The iteration part of the above answer is fine and I've accepted this as an answer.

Answer 2

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

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

Hope this helps!