VBA Solver Macro which repeats accross multiple collumns with IF constraints

[s.patelupi]

Hi, I was hoping someone could help me with some VBA.

Firstly, I will say I have found a similar example on this site however I cant work out what its doing and therefore I cant change it to meet my requirements, plus its missing additional constraints which I don't know how to do.

My current ability is intermediate, I am fairly good with excel but my VBA skills are all copy, paste and tweak and everything is self taught off other peoples code. I an a constant forum researcher and this is my first post asking for help so any would be greatly appreciated.

I am looking to run a solver query macro which repeats multiple times, I can do this manually by using the solver tool however as I have multiple columns (which could expand even further at a later date) this takes quite a long time.

This is what I'm trying to do (I have based my criteria on the first column, but I then want it to repeat until the column W):

Objective:
- Cell C67 (currently has a weighted average formula in) - i want the it to finish >80% but <100%

Variable Cells:
- Any cells between C35:c65 that have "No Data" in it

Constraints:
- No variable cells to exceed 100%
- If the row has a "ME" in column D then that cell is >58% but <70%
- If the day is a "Sat" in column C then cell is >58% but <75%
- If the day is a "Sun" in column C then cell is =0%

The excel doc I've attached is not actual data but just a test however I will use the layout as a template.

I would really appreciate if someone could not only answer the question but also include some commentary which will allow me to learn it (rather than copy and paste).

Thanks in advance.

[Alf]

Your uploaded excample has me baffled but as far as I understand you are looking for a macro to loop solver. So you need somthing like this:

Please Login or Register  to view this content.

As you wish your object cell to be > 80% and < 100% I would probably add this as a constraint.

Variable Cells:
- Any cells between C35:c65 that have "No Data" in it

Constraints:
- No variable cells to exceed 100%
- If the row has a "ME" in column D then that cell is >58% but <70%
- If the day is a "Sat" in column C then cell is >58% but <75%
- If the day is a "Sun" in column C then cell is =0%

How to model your other constraints I really don't know, as solver only crunch numbers I can't see how to set this up or perhaps you need to explain you constraint a bit more.

Alf

[s.patelupi]

Hi Alf

Thanks for coming back to me, sorry the test doc could have been made much simpler.

Appreciate you writing the above code, could you help with a few words of what each line is doing on the below ? that way I can try and either tweak or create the constraints myself? I can see where you have set the objective cell and also where you set the range but cant see how you made it >80 & <100 - is it anything to do with the below and if so could you give a quick explanation on what they mean?

Address, MaxMinVal:=3, ValueOf:="0", _
Address, Relation:=3, FormulaText:="0"

With regards to the remaining constraints, if I could work out how you are applying the above then Ill just make them a bit more specific to certain cells.

Thanks for your help.

J

[s.patelupi]

Following on, when I'm using the VB you created, its giving me a few errors, firstly, a few lines are in red but when I try to run the macro it also says sub or function not defined

[s.patelupi]

scrap the second error my references had reset on VB

[Alf]

to run solver using a macro you must first set a reference to solver in Visual Basic.

Open Visual Basic window, click "Tools" then select "References" in the small windows that opends ("References - VBA project") tick box marked "Solver". Now you can run Solver using a VBA macro.

Alf

Ps Will answer you other question a bit later to day.

[s.patelupi]

Following again, I solved it using a mix between what you provided and the macro create button, just using a few parameters this was the result (I made a few changes to the test document which is why some of the numbers differ).

Thanks for your help Alf.



Sub Solver()

Dim i As Integer
For i = 5 To 25

SolverReset
SolverOk SetCell:=Cells(57, i).Address, MaxMinVal:=3, ValueOf:=0.8, ByChange:=Range(Cells(43, i), Cells(55, i)).Address, Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:=Range(Cells(43, i), Cells(55, i)).Address, Relation:=1, FormulaText:="1"
SolverAdd CellRef:=Range(Cells(43, i), Cells(55, i)).Address, Relation:=3, FormulaText:="0.6"
SolverAdd CellRef:=Range(Cells(53, i), Cells(55, i)).Address, Relation:=1, FormulaText:="0.7"
SolverSolve UserFinish:=True

Next i
End Sub

[Alf]

SolverAdd(CellRef, Relation, FormulaText)
CellRef Required Variant. A reference to a cell or a range of cells that forms the left side of a constraint.
Relation Required Integer. The arithmetic relationship between the left and right sides of the constraint. If you choose 4, 5, or 6, CellRef must refer to decision variable cells, and FormulaText should not be specified.
Relation Arithmetic relationship
1 <=
2 =
3 >=
4 Cells referenced by CellRef must have final values that are integers.
5 Cells referenced by CellRef must have final values of either 0 (zero) or 1.
6 Cells referenced by CellRef must have final values that are all different and integers.
FormulaText Optional Variant. The right side of the constraint.

To let solver know that Cells(67, i) is the cell in row 67 and column 3 (for first value of i) i.e. cell C67 you add Address.

MaxMinVal:=1 means that the target value is set to max

MaxMinVal:=2 means that the target value is set to min

MaxMinVal:=3 means that the target value is set to a value that you specify.

This is a bit bevildering as 1 means target cell is set to maximum but in the SolverAdd(CellRef this means equal or less.

So in the row below the range C59:E59 should be equal to or greater than range C62:E62

SolverAdd CellRef:="$C$59:$E$59", Relation:=3, FormulaText:="$C$62:$E$62"

SolverAdd CellRef:="$C$8:$C$19", Relation:=4, FormulaText:="integer" so this relation is 4 as the numbers in C8:C19 is defined as intgers, if they were binaries the relation would be 5 and formula text would be "binary"

But normaly one should not be concerned about this. Start macro recording and set up your solver model and these references and relations will be added automatically. What you then need to do is to change fixed cell addresses to variable one if you are running solver in a loop.

Alf

[s.patelupi]

Thanks Alf, Can I ask one last thing - is it possible to link the cell ref range to a cell in my excel doc rather than have it set in code.

For example instead of putting 'Cells(67, i)' I could put 'Cells(**Refer to number in Cell AA1**, i) if I have this cell as a formula which will change according to what data I have it (and in turn give me the IF constraint that I was also looking for)?

Thanks

[Alf]

There are some constraints build into solver to make it run faster. All values used in the solver run must be on the active sheet where you run solver. And the cells to change must have an impact on the target cell when solver change them.

Could you use a macro to "import" data from your excel doc to solver sheet and then run solver as a workaround?

Solver and if constraint see cut from solver help file.

The most common discontinuous function in Excel is the IF function where the conditional test is dependent on the decision variables. Other common discontinuous functions are CHOOSE, the LOOKUP functions, and COUNT. Common non-smooth functions in Excel are ABS, MIN and MAX, INT and ROUND, and CEILING and FLOOR. Functions such as SUMIF and the database functions are discontinuous if the criterion or conditional argument depends on the decision variables.

A smooth nonlinear solver, such as the GRG Solving method, relies on derivative or gradient information to guide it towards a feasible and optimal solution. Since it is unable to compute the gradient of a function at points where the function is discontinuous, or to compute curvature information at points where the function is non-smooth, it cannot guarantee that any solution it finds to such a problem is truly optimal. In practice, the GRG method can sometimes deal with discontinuous or non-smooth functions that are “incidental” to the problem, but as a general statement, this Solving method requires smooth nonlinear functions for the objective and constraints.

Perhaps this link could be of interest:

http://www.solver.com/excel-solver-help

Alf