Solver VBA, how to paste "SolverSolve return value" for every row

Question

I have created the following macro, which loops through a set of rows optimising product price for every row.

Sub OptimizePrice()
Dim i As Integer
ActiveWorkbook.ActiveSheet.Activate
For i = 5 To 20
SolverReset
SolverOk SetCell:="$K$" & i, MaxMinVal:=1, ByChange:="$B$" & i, Engine:=1
SolverAdd CellRef:="$B$" & i, Relation:=1, FormulaText:="$E$" & i
SolverAdd CellRef:="$F$" & i, Relation:=1, FormulaText:="$G$" & i
SolverSolve True
Range("$L$" & i)= SolverSolve Return Value
Next i
End Sub

Sometimes Solver finds an optimal solution, sometimes it doesn't. I want my macro to PASTE for every row in column "L", the result of the operation, which ranges from 0 to 20 according to the description provided in the following webpage: https://www.solver.com/excel-solver-solversolve-function.

I've tried to achieve this with this line of code 'Range("$L$" & i)= SolverSolve Return Value' but it seems it's not the right way to get the returnvalue type.

Your help is appreciated!.

Will this work? Range("$L$" & i)= SolverSolve Return Value -> Cells(i, "L").Value = SolverSolve — Wizhi
Thanks it worked, I just modified my code based on your suggestion: Cells(i, "L").Value = SolverSolve. However I don't see a button to accept your post as an answer! Not sure if I have to wait — Juan Gut
I'm happy it worked :). Reason is that I didn't post it as a answer, but as a comment. I posted an answer (which can be accepted if you want to) and some small advice regarding the ActiveWorkbook.ActiveSheet.Activate. Happy coding :)!! — Wizhi

Wizhi Wizhi · Accepted Answer · 2020-07-28T08:12:04

Change this line:

Range("$L$" & i)= SolverSolve Return Value

Into something like this:

ws.Cells(i, "L").Value = SolverSolve

I would also recommend to use the declared variable of worksheet (ActiveWorkbook.ActiveSheet.Activate)

Reason is, if we use the above line, we want to make sure the code runs on the "correct" worksheet.

Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet1")
ws.activate 'Should be avoided but can be nice for now, since you might avoid to rewrite some code.

Solver VBA, how to paste "SolverSolve return value" for every row

1 Answers