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?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top