Solver VBA Loop

Hello, I am new to VBA and I am trying to create a Solver macro that loops and solves for each row until the end. My Solver code is below.



Sub ASPs()

SolverReset
SolverOK MaxMinVal:=0, ValueOf:=0, ByChange:="$AR$5:$AS$5", Engine:=1, _
EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$AW$5", Relation:=2, FormulaText:="$AT$5"
SolverOK MaxMinVal:=0, ValueOf:=0, ByChange:="$AR$5:$AS$5", Engine:=1, _
EngineDesc:="GRG Nonlinear"
SolverOK MaxMinVal:=0, ValueOf:=0, ByChange:="$AR$5:$AS$5", Engine:=1, _
EngineDesc:="GRG Nonlinear"
SolverSolve UserFinish:=True
End Sub




One of the solutions I have seen is the one below but I can't get the FormulaText to continue in the Loop.


Sub ASPLoop()


Dim cellChange As Range
Dim cellGoal As Range
Dim cellConstraint As Range

Set cellChange = ActiveSheet.Range("AR5:AS5")
Set cellGoal = ActiveSheet.Range("AW5")
Set cellConstraint = ActiveSheet.Range("AT5")

Do '********* LOOP & SOLVE ***************
SolverReset
SolverOptions Precision:=0.001
SolverOK SetCell:=cellGoal.Address(True, True), _
MaxMinVal:=1, ByChange:=cellChange.Address(True, True)
SolverAdd CellRef:=cellConstraint.Address(True, True), _
Relation:=2, FormulaText:="AT5"
Solver.SolverSolve UserFinish:=True

Set cellChange = cellChange.Offset(1, 0)
Set cellGoal = cellGoal.Offset(1, 0)
Set cellConstraint = cellConstraint.Offset(1, 0)

Loop While Trim(cellGoal.Text) <> "" 'until goal cell is empty
End Sub

I have attached my workbook and when you press the Macro button that I assigned the Macro to, it fills out what I need to columns AR and AS, but it only does for the first row. I am trying to make it so it loops and does it for all rows.

Thank you,

I have attached my workbook and when you press the Macro button that I assigned the Macro to, it fills out what I need to columns AR and AS, but it only does for the first row. I am trying to make it so it loops and does it for all rows.

Thank you,

I am not sure what you mean by chaning to any random value. The number of procedures for cervical and lumbar is given as well as the total for each row. The Solver would work for each row if I used it for each row. Is there not a way to make a simple loop to make it solve for each row?

Thank you for your help.

I see your point with there being 3 options. I think that maybe Columns AU and AV can be ignored. I didn't understand that because if there were cervical cases performed, there wouldn't be zero revenue earned. The only place there would be zero revenue is where there are zero cases performed. I was using the formula of
X(# of Cervical Cases)+Y(# of Lumbar Cases)=Total Revenue.
Where X=Cervical ASP and Y=Lumbar ASP. Is there a way to Loop is the way I did with the Solver macro if we ignore the Cervical revenue(column AU) and Lumbar revenue(Column AV) in order to focus on only the total revenue so there is only one option?

Thank you for clearing that up. Do you know why when I use the Solver, it returned 13,067 for my X and 16,215 for my Y? Is that because it auto-defaults to dividing the constraint in half making X and Y as close to each other as possible?

Thank you for your time and explaination.