Looping a Solver

Question

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.

Алексей Р · Answer

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

Pablo Cardoso · Answer

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

Looping a Solver

2 Answers