VBA Loop in Excel with Solver

Question

So here is my issue. I am trying to write a loop that performs a function using solver. Basically it starts in one cell, runs solver, moves the target and constraint cells to the right one, solves, moves target and contraint cells to the right 14, solves, right one, solves, right 14 solves, until it finds a blank.

Here is the first few lines of code to show you what I am doing in the long version.

Range("RI6").Select
    SolverOk SetCell:="$RI$16", MaxMinVal:=3, ValueOf:=0.15, ByChange:="$RG$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$RI$1", Relation:=3, FormulaText:="74"
    SolverAdd CellRef:="$RI$1", Relation:=1, FormulaText:="87"
    SolverSolve (True)
Range("RJ16").Select
    SolverOk SetCell:="$RJ$16", MaxMinVal:=3, ValueOf:=0.15, ByChange:="$RJ$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$RJ$1", Relation:=3, FormulaText:="74"
    SolverAdd CellRef:="$RJ$1", Relation:=1, FormulaText:="87"
    SolverSolve (True)
Range("RX16").Select
    SolverOk SetCell:="$RX$16", MaxMinVal:=3, ValueOf:=0.15, ByChange:="$RX$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$RX$1", Relation:=3, FormulaText:="74"
    SolverAdd CellRef:="$RX$1", Relation:=1, FormulaText:="87"
    SolverSolve (True)
Range("RY16").Select
    SolverOk SetCell:="$RY$16", MaxMinVal:=3, ValueOf:=0.15, ByChange:="$RY$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$RY$1", Relation:=3, FormulaText:="74"
    SolverAdd CellRef:="$RY$1", Relation:=1, FormulaText:="87"
    SolverSolve (True)
Range("SM16").Select
    SolverOk SetCell:="$SM$16", MaxMinVal:=3, ValueOf:=0.15, ByChange:="$SM$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$SM$1", Relation:=3, FormulaText:="74"
    SolverAdd CellRef:="$SM$1", Relation:=1, FormulaText:="87"
    SolverSolve (True)
Range("SN16").Select
    SolverOk SetCell:="$SN$16", MaxMinVal:=3, ValueOf:=0.15, ByChange:="$SN$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$SN$1", Relation:=3, FormulaText:="74"
    SolverAdd CellRef:="$SN$1", Relation:=1, FormulaText:="87"
    SolverSolve (True)
Range("TB16").Select
    SolverOk SetCell:="$TB$16", MaxMinVal:=3, ValueOf:=0.15, ByChange:="$TB$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$TB$1", Relation:=3, FormulaText:="74"
    SolverAdd CellRef:="$TB$1", Relation:=1, FormulaText:="87"
    SolverSolve (True)
Range("TC16").Select
    SolverOk SetCell:="$TC$16", MaxMinVal:=3, ValueOf:=0.15, ByChange:="$TC$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$TC$1", Relation:=3, FormulaText:="74"
    SolverAdd CellRef:="$TC$1", Relation:=1, FormulaText:="87"
    SolverSolve (True)
Range("TQ16").Select
    SolverOk SetCell:="$TQ$16", MaxMinVal:=3, ValueOf:=0.15, ByChange:="$TQ$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$TQ$1", Relation:=3, FormulaText:="74"
    SolverAdd CellRef:="$TQ$1", Relation:=1, FormulaText:="87"
    SolverSolve (True)
Range("TR16").Select
    SolverOk SetCell:="$TR$16", MaxMinVal:=3, ValueOf:=0.15, ByChange:="$TR$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$TR$1", Relation:=3, FormulaText:="74"
    SolverAdd CellRef:="$TR$1", Relation:=1, FormulaText:="87"
    SolverSolve (True)
Range("UF16").Select
    SolverOk SetCell:="$UF$16", MaxMinVal:=3, ValueOf:=0.15, ByChange:="$UF$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$UF$1", Relation:=3, FormulaText:="74"
    SolverAdd CellRef:="$UF$1", Relation:=1, FormulaText:="87"
    SolverSolve (True)
Range("UG16").Select
    SolverOk SetCell:="$UG$16", MaxMinVal:=3, ValueOf:=0.15, ByChange:="$UG$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$UG$1", Relation:=3, FormulaText:="74"
    SolverAdd CellRef:="$UG$1", Relation:=1, FormulaText:="87"
    SolverSolve (True)

Thank you in advance for any help / advice.

You don't seem to have any question. Does your code actually work? If so -- you can shorten it by using a loop. — John Coleman
– John Coleman, Commented Mar 14, 2016 at 20:00
If you need help writing loops then you might want to look at the following website: homeandlearn.org/excel_vba_for_loops.html — Ralph
– Ralph, Commented Mar 14, 2016 at 20:10
My question is how to do the cell movement, I have tried to loop that and cannot get it working properly. The code works when written out the long way, but not when I have attempted a loop. — Mark Simmons
– Mark Simmons, Commented Mar 14, 2016 at 20:26

mongoose36 · Accepted Answer · 2016-03-14 23:00:18Z

1

Untested but perhaps something like...

Option Explicit

Sub SolverLop()
Dim i As Long
Dim cnt As Integer
cnt = 0
For i = 477 To ActiveSheet.UsedRange.Columns.Count

    SolverReset
    SolverOk SetCell:=Cells(16, i).Address, MaxMinVal:=3, ValueOf:=0.15, ByChange:=Cells(16, i).Address, _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:=Cells(1, i).Address, Relation:=3, FormulaText:="74"
    SolverAdd CellRef:=Cells(1, i).Address, Relation:=1, FormulaText:="87"
    SolverSolve Userfinish:=True

    cnt = cnt + 1
    If cnt = 2 Then
        i = i + 14
        cnt = 0
    End If
Next

End Sub

Make sure you have a reference to solver checked in Tools--> References--> Solver.

P.S. 477 is the column number associated with the starting column, "RJ", in your example above...To get the column number of any column just type the following into a blank cell =column(indirect("A2")) ... Replace "A2" with the column you are wanting to find the number for.

edited Mar 14, 2016 at 23:00

answered Mar 14, 2016 at 21:06

mongoose36

7994 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

n8. Over a year ago

I was working on something really similar but yours is cleaner. You should share that 477 is the column number, or input a conversion so OP can just input the starting range and the column # is gathered from that.

Demetri · Accepted Answer · 2016-03-14 21:01:39Z

The first part looks different then the others, so I did not include it in the "loop", but it would be easy enough to add, if the differences were due to a typo.

    Range("RI6").Select
    SolverOk SetCell:="$RI$16", MaxMinVal:=3, ValueOf:=0.15, ByChange:="$RG$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$RI$1", Relation:=3, FormulaText:="74"
    SolverAdd CellRef:="$RI$1", Relation:=1, FormulaText:="87"
    SolverSolve (True)

So one way to loop would be as below:

    Dim Temp As String, TempArray As Variant

    TempArray = Array("RJ16", "RX16", "RY16", "SM16", "SN16", "TB16", "TC16", "TQ16", "TR16", "UF16", "UG16")
    For i = 0 To UBound(TempArray)
        Range(TempArray(i)).select
        Temp = Cells(1, Range(TempArray(i)).Column).Address
        SolverOk SetCell:=TempArray(i), MaxMinVal:=3, ValueOf:=0.15, ByChange:=Temp, Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverAdd CellRef:=Temp, Relation:=3, FormulaText:="74"
        SolverAdd CellRef:=Temp, Relation:=1, FormulaText:="87"
        SolverSolve (True)
    Next i

Collectives™ on Stack Overflow

VBA Loop in Excel with Solver

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related