CODEWITHSUNDEEP
vbaexcelfor-loopsolver
bio_bio
bio_bio

Reputation: 112

VBA Solver Loop Keeps Only Last Loop Results

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

Upvotes: 0

Views: 578

Answers (2)

Stephen Morrell
Stephen Morrell

Reputation: 1201

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!

Upvotes: 1

Vityata
Vityata

Reputation: 43585

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.

Upvotes: 0

Related Questions