VBA code for Solver optimization has debug errors

Question

Code Snippet:

Sub SolveOptimization()

Dim ws As Worksheet 

Dim targetCell As Range 

Dim variableRange As Range 

' Set the worksheet (change "Sheet1" to your sheet name) 

Set ws = ThisWorkbook.Sheets("Sheet1") 

' Set the objective cell (U6) 

Set targetCell = ws.Range("U6") 

targetCell.Value = 0 

' Set the variable cells (B3:U3, B5:U5, B9:U9, B10:U10) 

Set variableRange = Union(ws.Range("B3:U3"), ws.Range("B5:U5"), ws.Range("B9:U9"), ws.Range("B10:U10")) 

' Clear any existing Solver settings 

Application.Run "Solver.xlam!SolverReset" 

' Set up Solver parameters 

Application.Run "Solver.xlam!SolverOk", SetCell:=targetCell.Address, MaxMinVal:=2, ByChange:=variableRange.Address 

' Add constraints 

Application.Run "Solver.xlam!SolverAdd", CellRef:=ws.Range("B6:U6"), Relation:=2, FormulaText:="0" 

Application.Run "Solver.xlam!SolverAdd", CellRef:=ws.Range("B12:U12"), Relation:=4, FormulaText:=ws.Range("C12").Formula 

Application.Run "Solver.xlam!SolverAdd", CellRef:=ws.Range("B7:U7"), Relation:=3, FormulaText:="1" 

Application.Run "Solver.xlam!SolverAdd", CellRef:=ws.Range("B8:U8"), Relation:=3, FormulaText:="1" 

Application.Run "Solver.xlam!SolverAdd", CellRef:=ws.Range("B5:U5"), Relation:=1, FormulaText:="100000" 

' Run Solver and wait for user to finish solving 

Application.Run "Solver.xlam!SolverSolve", UserFinish:=True 

' Keep the solver solution 

Application.Run "Solver.xlam!SolverFinish", KeepFinal:=1 

End Sub

Code Question:

Objective cell: U6 'Set value to 0'

Variable cells: B3:U3; B5:U5; B9:U9; B10:U10

Constraint 1: B6:U6 = 0

Constraint 2: B12=C12=D12=E12=F12=G12=H12=I12=J12=K12=L12=M12=N12=O12=P12=Q12=R12=S12=T12=U12 (OR) B12:U12 = '=Average(B12:U12)'

Constraint 3: B7:U7 =>1

Constraint 4: B8:U8 =>1

Constraint 5: B5:U5 <=100000

VBA code to satisfy the Objective & Constraints through Variable cells.

Code Execution OUTCOME:

Sub SolveOptimization() - highlighted in Yellow

Compile error: Named argument not found ('SetCell:=' highlighted in blue.) Etc.

This code is not executing even after debugging online. Somehow, Worksheet functions are not working properly for me. Loop or Iteration sometimes given me mediocre results in other codes.

Answer 1

Try

Application.Run "Solver.xlam!SolverOk", targetCell.Address, 2, , variableRange.Address

or if you set a reference to Solver in Tools > References:

SolverOk SetCell:=targetCell.Address, MaxMinVal:=2, ByChange:=variableRange.Address