Complete Solver by row in Macro

Perhaps something like this? But untested so it may not work. In this case an uploaded file is needed to fix code problem. This code is based on rows going from 2 to 150 modify as needed.

I assume you have set a reference to solver in VBA.

Option Explicit

Sub SolvLoop()
Dim i As Integer

For i = 2 To 150

SolverReset

SolverOk SetCell:="$AC$" & i, MaxMinVal:=1, ByChange:="$H$" & i & "," & "$J$" & i & "," & "$L$" & i, Engine:=2
SolverAdd CellRef:="$AD$" & i, Relation:=1, FormulaText:="3"
SolverAdd CellRef:="$H$" & i, Relation:=2, FormulaText:="=(1" & "-$J$" & i & "-$L$" & i & ")"
SolverAdd CellRef:="$J$" & i, Relation:=2, FormulaText:="=(1" & "-$H$" & i & "-$L$" & i & ")"
SolverAdd CellRef:="$L$" & i, Relation:=2, FormulaText:="=(1" & "-$H$" & i & "-$J$" & i & ")"

Next

End Sub

When posting you should wrap your code. Click on # icon and write your code in between "["Code"]" "[/"Code"]" symbols you see. (Forum rule!!!!)

Alf

As you file is an .xls file I assume that you are running Excel 2003 and the command "Engine:=2, EngineDesc:="Simplex LP" was first used in Excel 2010, excel 2003 will not understand this command.

After opening the file in excel 2003 and setting a VBA reference to "Solver.xla" and adding the command to run solver in Simplex mode it looks like your model work and the settings you wanted can be seen in the solver window for the last row i.e row 118. The macro "SolvLoopMod" will run on Excel 2003 as well as Excel 2010 but I've not made any comparing between the two result to see how well they match.

I will also recommend you to change the calculating settings to "Automatic" in stead of the "Manual" setting on the uploaded file. I've changed that in this uploaded file.

As help given will depend on your Excel version I would suggest you update your forum user profile to show the excel version you use.


Alf