VBA Solver Loop Keeps Only Last Loop Results

Question

Sorry, this is another VBA Solver looping problem. I've read many of the other questions/answers posted here and elsewhere, but being new to VBA (this is the first thing I am attempting), I'm unable to pinpoint my error.

I wish to set cell Ii to 0 while changing cells Ji and Ki (keeping results), where i are rows 3 to 21.

My current code does not come up with any errors, but the results only keep on the last row of the loop- please advise! I've tried using range() and range.offset (from other examples) instead of cells(), and also setting the active worksheet to no avail.

I am using Excel 2011 for Mac.

Sub SolveTwo()
    'Not sure if this is necessary
    Dim row As Integer
    'Begin loop
    For row = 3 To 21
        'Test code shows it is stepping through loop
        Cells(row, "U").Value = row
        'Grab starting values from other columns
        Cells(row, "J").Value = Cells(row, "S").Value
        Cells(row, "K").Value = Cells(row, "T").Value
        'Solver Code
        SolverReset
        SolverOptions Precision:=1e-05
        SolverOk SetCell:=Cells(row, "I").Address, _
        MaxMinVal:=3, ValueOf:=0, _
        ByChange:=Cells(row, "J").Address & "," & Cells(row, "K").Address, _
        Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve UserFinish:=True
        SolverFinish KeepFinal:=1
        'Not sure if below is necessary
        'SolverSave SaveArea:=Cells(row, "J").Address & "," & Cells(row,"K").Address
    Next row
End Sub

Stephen Morrell · Accepted Answer · 2019-03-18 13:39:33Z

Had the same problem, i.e. only retaining the solutions to the last solver call.

It's caused by Excel for Mac's solver operating asynchronously, and the solver macro only starts once the calling code has completed. Hence the solver parameters are reset repeatedly by the calling code, but the solver doesn't run until the last iteration.

There is no solution forthcoming currently but here are two workarounds. The first one is to have two modules: a regular module calling solver once, and a second class module which fires whenever the sheet calculates (solver kicks off a re-calc when finishing), and calls the second one. Iterate back and forth in a loop. See here for great solution by J Peltier which I've admittedly not tried: solution 1

Solution 2 which I used is to call solver from an Apple Script. Here's an example. The control flow in the macro uses worksheet cells for the loop counters etc, and my macro was called by shift-opt-cmd-O. My solver usually finished in 10 sec, so I waited 15.

on run {input, parameters}
-- Click “Microsoft Excel” in the Dock.
set timeoutSeconds to 2.0
set uiScript to "click UI Element \"Microsoft Excel\" of list 1 of application process \"Dock\""
my doWithTimeout(uiScript, timeoutSeconds)
-- Press ⇧⌥⌘O
repeat 496 times
    set timeoutSeconds to 2.0
    set uiScript to "keystroke \"Ø\" using {shift down, option  down, command down}"
    my doWithTimeout(uiScript, timeoutSeconds)
    delay 15
    say "done"
end repeat
return input
end run

Hope that helps!

Vityata · Accepted Answer · 2017-11-20 16:57:09Z

0

Try something like this:

Sub SolveTwo()

    Dim myRow As Integer

    For myRow = 3 To 21
        With Worksheets(2)
            .Cells(myRow, "U") = myRow
            .Cells(myRow, "J") = Worksheets(1).Cells(myRow, "S")
            .Cells(myRow, "K") = Worksheets(1).Cells(myRow, "T")
        End With

        'add your solver code here.

    Next myRow
End Sub

It will generate some results in Worksheets(2), if the sheet you are executing is the first one. Furthermore, do not use Row as a variable name, because it is used in the VBEditor.

answered Nov 20, 2017 at 16:57

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

2 Comments

bio_bio Over a year ago

It still only keeps the results in J21 and K21, none of the other rows, even though it seems to compute them.

bio_bio Over a year ago

I've amended the "row" variable though. :)

Collectives™ on Stack Overflow

VBA Solver Loop Keeps Only Last Loop Results

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related