Solver to run multiple columns separately

benlandgren

New Member
Joined
Dec 7, 2017
Messages
2
Hi, I have made a spreadsheet where all columns are similar in terms of formulas etc. Only the input values for each column are different. Each column has 4 variable cells that the solver needs to find a solution for. I have tried to make a solver setup that finds solution for the 4 variable cells of all of the columns but it seems to work only sometimes and seems to be computationally taxing.

As all of the columns are independent from each other, I was looking to find a solution to run the solver separately, one column at a time, but I have not been able to find any help on how this could be done. I have seen this been done with goal seek, but I would prefer solver.

I am also quite new to VBA.

Any idea how this could be done?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

benlandgren

New Member
Joined
Dec 7, 2017
Messages
2
To clarify the issue more:
I have created a solver that works well for the first column. Then I recorded a macro from it and according to some instructions that I found I managed to make it work more or less for the rest of the columns. I want the target value to become zero. But for some reason it does not become exactly zero. How can I make the target value become more accurate (closer to zero)?

Here is the code I used:

Sub SolvLoop()


Dim i As Long


For i = 52 To Range("D" & Rows.Count).End(xlUp).Row


SolverReset
SolverOk SetCell:="D" & i, MaxMinVal:=3, ValueOf:=0, ByChange:="$D$41:$D$44", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$D41:D44$", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$D48:D51$", Relation:=2, FormulaText:="0"
SolverSolve Userfinish:=True
Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,550
Members
414,155
Latest member
Grainne whiteside

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top