Looping a Solver-CodePudding

I am having some trouble trying to loop the Solver for "ma" varying from 2 to "n"a in the code below, in which "ma" is a preset number which is equal the number of columns in the sheet "Dados Brutos - A". The problem happens by the time I try to configure the argument "ByChange". I want to define there a range from cells column "na" (A, B, C and so on) and rows 6, 7 and 8. Can anybody help me? I've attached a print to show what exactly I am aiming to do.

Edit:

The code below solved the problem, thanks to Алексей Р. Hope this post helps anyone else having the same problem.

Sub AjustarCurvas_A()
'
' Ajusta equações de segundo grau às amostras selecionadas
'

'
    Dim na As Integer, ma As Integer
    
    Sheets("Dados Brutos - A").Select
    na = Cells.Find(What:="*", SearchDirection:=xlPrevious).Column
    
    Sheets("Det. Ruído - A").Select
    
    With ActiveSheet
        For ma = 2 To na
            SolverOk SetCell:=.Cells(2, ma), MaxMinVal:=2, ValueOf:=0, ByChange:=.Cells(6, ma).Resize(3), _
                Engine:=1, EngineDesc:="GRG Nonlinear"
            SolverSolve (True)
        Next ma
   End With
End Sub

Here are prints of sheets "Dados Brutos - A" and "Det. Ruído - A"

Dados Brutos - A Det. Ruído A

What I did was count how many columns I had on "Dados Brutos - A" and called that number "na". Then I used the solver ("na" - 1) times to vary the values on cells (B6:B9) and optimize the value calculated from them on cell B14. Then (C6:C9) to optimize C14 and so on, until I reached column CN, in that specific case.

CodePudding user response：

The task is not completely clear, but tried to make the code workable. In particular, in the code there are names of two sheets, and a screenshot of only one. The objective function of the search for a solution is not clear - what is the relationship between cells C5:C7 and C8. Added debug message Debug.Print ... to monitor progress

Sub Macro1()
    Dim n As Integer, m As Integer
    
    ' the purpose of this piece of code is not clear
'    Sheets("Dados Brutos - A").Select
'    n = WorksheetFunction.CountA("B:B")
    
    n = 3 ' for example
    
    With ActiveWorkbook.Worksheets("A") ' "Det. Ruído - A" - my excel does not accept one of the characters
        For m = 2 To n
            Debug.Print "Trying to process range [" & .Cells(5, m).Resize(3).Address & "] to minimize value in cell " & .Cells(8, m).Address
            SolverReset

            SolverOk SetCell:=.Cells(8, m), MaxMinVal:=2, ByChange:=.Cells(5, m).Resize(3), _
                Engine:=1, EngineDesc:="GRG Nonlinear"
            SolverSolve
            
            ' not working - message window will be appear
            Application.DisplayAlerts = False
        Next m
    End With
End Sub

Prints:

Trying to process range [$B$5:$B$7] to minimize value in cell $B$8
Trying to process range [$C$5:$C$7] to minimize value in cell $C$8

CodePudding user response：

Problem solved. The solution is detailed as an "edit" in the question.