I would like to create a VBA code which,

a. Includes a formula within the code for the variable which is referenced in the SetCell section of the Solver Function (this is what am looking to maximize).

b. Uses Arrays for the SetCell variable and ByChanging cells variable of the Solver function.

Right now my code works but takes more than 1 hour for 10,000 rows and the problem am trying to solve has more than 100,000 rows. I believe items a. and b. above will speed up my code.

Is there a way to achieve a. and b. above?

So far what has worked is including an “Output” variable (variable am looking to maximize) within the Excel file which has a formula and referencing this variable in the code. See image of my data and code tried below. Note: This code is a simplified example of the real problem.

I believe this would run faster if a. and b. referenced above are done.
Is there a way to do this?

Image of my data

Below is the code that works but is too slow for the real problem that has more than 100,000 rows and several more input variables:

Option Explicit

Sub Solver_Test()
'
' Solver_Test Macro
'

Dim startTime As Single
Dim Goal(2 To 11) As Variant
Dim Input1 As String
Dim arr As Variant
Dim i As Long
Dim Sh As Worksheet

Set Sh = ThisWorkbook.Worksheets("Sheet1")

   Application.Calculation = xlCalculationManual
   Application.ScreenUpdating = False
   Application.EnableEvents = False
   
   arr = Sh.Range("A1").CurrentRegion
   
   For i = 2 To UBound(arr, 1)
   
   Goal(i) = Sh.Range("B" & i).Address
   Input1 = Sh.Range("C" & i).Address
   
   SolverReset
   
   SolverOk Goal(i), 1, 0, Input1, 1
   
   SolverAdd Input1, 3, 0.1
   SolverAdd Input1, 1, 0.8


SolverSolve True

Next i

   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
   Application.EnableEvents = True
   
Debug.Print Timer - startTime

'
End Sub

Below is what I attempted with the aim of making the code run faster by incorporating items a. and b. above and got an error: “Error in model. Please verify that all cells and Constraints are valid. Perhaps some cells that are not Variable Cells are marked as Integer, Binary, or AllDifferent”

Sub Solver_Test1()
'
' Solver_Test1 Macro
'

Dim startTime As Single
Dim Goal(2 To 11) As Variant
Dim Input1 As String
Dim Input2 As String
Dim Input3 As String
Dim Input4 As String
Dim Input5 As String
Dim arr As Variant
Dim i As Long
Dim Sh As Worksheet

Set Sh = ThisWorkbook.Worksheets("Sheet1")

   Application.Calculation = xlCalculationManual
   Application.ScreenUpdating = False
   Application.EnableEvents = False
   
   arr = Range("A1").CurrentRegion
   
   For i = 2 To UBound(arr, 1)
   
   Input1 = Sh.Range("C" & i).Value
   Input2 = Sh.Range("D" & i).Value
   Input3 = Sh.Range("E" & i).Value
   Input4 = Sh.Range("F" & i).Value
   Input5 = Sh.Range("G" & i).Value
   
   Goal(i) = (2.71828 ^ ((Input1 - Input2) * (0.5 * Input3 + 0.2 * Input4)) / (1 + 2.71828 ^ ((Input1 - Input2) * (0.5 * Input3 + 0.2 * Input4)))) * (Input1 - Input5)
   
   SolverReset
   
   SolverOk Goal(i), 1, 0, Input1, 1
   
   SolverAdd Input1, 3, 0.9
   SolverAdd Input1, 1, 1

SolverSolve False

Next i

   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
   Application.EnableEvents = True
   
Debug.Print Timer - startTime

'
End Sub