Running Solver on Multiple Rows

[Boilermaker24]

Hi Everyone,

I am working on a problem to update pricing throughout a database of about 800 parts. After looking around online, I think Solver is the best way to approach this problem, but I couldn't find any VBA code to execute this.

Here are the rules (also illustrated below)
Primary Logic: 15% <= Core <= 35% Re Price
Secondary Logic: 85% <= Re Price + Core Price <= 95% Original Price

I pulled the data from row 4 of the spreadsheet and included the formulas:

D (Original Price)	G (Re Price)	I (Old Core Price)	T (Primary Low %)	U (Primary High %)	V (Secondary Low %)	W (Secondary High %)	X (Core)	Y (Re + Core Price)	Z (New Core)
$502.34	$146.97	$10.00	$22.05	$51.44	$426.99	$477.22	$51.44	$198.41	$51.44
			=G4*0.15	=G4*0.35	=D4*0.85	=D4*0.95		=G4+X4	=X4

X4 <= U4
X4 >= T4
Y4 <= W4
Y4 >= V4

Changing Variable Cells: X4
Set Objective (Max): Z4

I am looking to replicate this down about 800 rows of data to find the max core price for each part given the constraints. I am not looking to manually update solver each time and was thinking there was a better way to do this through VBA.

Thanks,
Isaac

[MrShorty]

I am a little surprised that you found nothing, because there is a lot of examples of VBA code running Solver out there. Search engines, I guess, just sometimes fail to find stuff that is right under their noses.

A good starting tutorial for Solver in VBA: https://peltiertech.com/Excel/SolverVBA.html
Some examples from our forum: https://www.excelforum.com/excel-pro...ithin-vba.html
https://www.excelforum.com/excel-pro...e-columns.html
https://www.excelforum.com/excel-pro...lver-loop.html
There are many others.

I suggest you start there and see how far you get. Let us know where you get stuck, and we'll help you get over the bumps along the way.

[Boilermaker24]

Hi MrShorty,

Thank you for the reference links, there is a slight chance I just didn't know what I was looking for.

I recorded a macro setting up solver:

Sub Solver_Max()
'
' Solver_Max Macro
'

'
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$X$2", Engine:=2 _
        , EngineDesc:="Simplex LP"
    SolverAdd CellRef:="$X$2", Relation:=3, FormulaText:="$T$2"
    SolverAdd CellRef:="$X$2", Relation:=1, FormulaText:="$U$2"
    SolverAdd CellRef:="$Y$2", Relation:=3, FormulaText:="$V$2"
    SolverAdd CellRef:="$Y$2", Relation:=1, FormulaText:="$W$2"
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$X$2", Engine:=2 _
        , EngineDesc:="Simplex LP"
    SolverOk SetCell:="$Z$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$X$2", Engine:=2 _
        , EngineDesc:="Simplex LP"
    SolverSolve True
End Sub

Now on to the part of automating solver through the next 826 rows. What would be a good starting point? I have tried to copy and paste the format from your examples, but am getting confused since they are different formulas.
Could you please help me out? I appreciate the response.

[MrShorty]

I'm sure we can help. However, this forum can get strict about rules, like putting code in code tags. You should edit your post to include those code tags.

I might also suggest that we look at this problem a little closer -- using our old favorite algebra instead of programming. At first glance, it appears to me that there ought to be an algebraic solution that will almost certainly be a lot easier to program into the spreadsheet than trying to loop Solver.

[Boilermaker24]

Thank you for the reminder. The code has been edited to include the code tags.
I am not seeing an algebraic solution that maximizes the core price. What direction were you thinking about going with this problem?

[MrShorty]

I see a system of 2 inequalities (column letters as variables):

1) 0.15G <= Z <= 0.35G
2) 0.85D <= G+Z <= 0.95D --> subtract G 0.85D-G <= Z <= 0.95D-G

At this point, the max value of Z that solves the first equation should be Z=0.35G and the max value that solves equation 2 should be Z=0.95D-G. It should be a simple decision (=IF()) step to decide which of the two possible solutions solves both inequalities.

I note that the problem in the OP does not seem to have a solution -- I cannot see a value for Z that fits in both ranges. The current value for Z appears to be the max value allowed by the first equation, and Y is still less than V. That could mean that I am not understanding the problem. What do you expect the solution for the problem in the OP to be?

[Boilermaker24]

Yes, it looks like this specific example would be unsolvable. There are a few rows that will need to be changed due to not fitting the constraints.
Okay, I understand where you're going with the algebra. It might be a number within the range of 15-35% that solves both equations though and that's why I would prefer to use Solver for this problem.
Could you assist me getting a start with the VBA route?

[MrShorty]

It might be a number within the range of 15-35% that solves both equations though and that's why I would prefer to use Solver for this problem.

Do you have an example of a problem where the solution is not one of the endpoints of the allowed ranges? I recognize that the problem statement technically allows for a solution in between values, but it seems like the solution will always be at one of those endpoints.

As for getting started with the VBA, I'm not sure what parts of the formula are confusing. I would expect something like:

Sub Solvertest()
Dim strsetcell as string, strbychange as string, strcontraint1 as string, etc.
For itcount=2 to 826
strsetcell="$Z$"&itcount
strbychange="$X$"&itcount
strcontraint1="$T$"&itcount
'and so on
SolverReset
SolverAdd CellRef:=strbychange, Relation:=3, FormulaText:=strcontraint1
'and so on for all constraints
SolverOk SetCell:=strsetcell, MaxMinVal:=1, ValueOf:=0, ByChange:=strbychange, Engine:=2 _
        , EngineDesc:="Simplex LP"
SolverSolve True
Next itcount
end sub

Note that all I have done was to put your Solver code into a For..Next loop, and replace the string constant references with variables that I can manipulate in the loop.