Speed up a looping solver VBA-CodePudding

Currently I have a column in which I am trying to keep at the max value whilst varying another one, within constraints. I have the code below. The code is needed to run for 450 cells and at the moment it is taking a while to complete, hence why it is set to 100. Is there a faster way to run this code, such as my poor syntax or by maybe setting the columns to an array? I haven't been using VBA that long and therefore don't fully understand the ins and outs.

Sub solve()

Dim i As Integer
For i = 16 To 100


' solve Macro

    'Reset Solver
    SolverReset
    
    'allows the HEX value to be changed via sheet not code
    mytarget = Range("$C$10")
   
    'constrains solver
    SolverAdd CellRef:="$J$" & i, Relation:=1, FormulaText:="$O$8"
    SolverOk SetCell:="$P$" & i, MaxMinVal:=3, ValueOf:=mytarget, ByChange:="$J$" & i, _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve (True)
    
    SolverSolve userFinish:=True
    SolverFinish KeepFinal:=1
    
    
Next i

    
End Sub

I have tried to disable automatic calculations and screen updating but this didn't have a massive effect on the speed of the process.

CodePudding user response：

Solver does complex calculation, if you need to do it hundreds of times, it'll take a while. I don't think there is an easy solution here.

Unless you know the formula behind and can find a more efficient way of finding the target.