Excel solver macro

Hi

I have a large document, that need to be calculated with excel solver. I record a macro and now i have problem, because each scenarios (based on dates in C6) have a different number of rows that need to be calculated with solver.


i need a macro that will automatical change variable cells and also range of constraint based on cell range in T4:Z44 and constraints range ab4:af44. So solver will calculate only cells with values in.


Example

date 1: 31.7.2017 show data in table range T4:Z14
changing variable cell are set from X4:X13
constraint are:
AD4:AD10<=20
W45=C5
X4:X13 <= 20
X4:X13 >= 5


macro for that part is written and it is in attachement file

SolverOk SetCell:="$AB$3", MaxMinVal:=1, ValueOf:=0, ByChange:="$X$4:$X$13", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$AB$3", MaxMinVal:=1, ValueOf:=0, ByChange:="$X$4:$X$13", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve

but if i change date in cell C6 to 4.9.2017 i have data in range T4: Z16. And macro that i'm lookin for will change variable cells from previus to current range of cell,
for example changing variable cell will be X4:X16
constraint will be:
AD4:AD10<=20
W45=C5
X4:X16 <= 20
X4:X16 >= 5

any suggestion ?

Assuming that you first select the date you could perhaps use something like this:

Sub Find_number()
Dim i As Integer

Range("X4").Activate
Selection.End(xlDown).Select
i = ActiveCell.Row()

SolverReset
SolverOk SetCell:="$AB$3", MaxMinVal:=1, ValueOf:=0, ByChange:="$X$4:$X$" & i, Engine:=1
    
    SolverSolve

End Sub

The calculation of i assumes that there are no blank cells in the range X4 to X & i. I would recommend that you add the line "SolverReset" at the start of the solver macro as this clears all old
in data from solver.

As a recording of a solver setup adds repetitive lines of solver commands I've deleted these. And you only need to add what engine you wish to use i.e. "Engine:=1", or "EngineDesc:="GRG Nonlinear" not both of them as a recorded macro will do.

Then you need to set up the constraint you mentioned above.

Alf

Thanks for reply.

I'm not sure if I understand this, while I get message from solver ''The Objective Cell values do not converge''. I'm quite new with solver programming

Process is in that order:
1) i select date in C6
2 and based on that date there is large list of cells in range T4:Z? . Formulas are in the background, and show values which belong to certain date. And that values are calculated by solver. Every scenario has different range of rows (14 rows, 16 rows, 10 rows,....)

3) Macro adjust changing variable cells to to the line with last full row. X4:X14 then if next date have more rows to X4:X16,....
and same with constraint

4) Macro run the solver and show results.


In attached file there is working solver model. It work manualy or by macro but just for this selected date. If I change date and that date have different range of rows, then i have to manualy change variable cells and same with constraint. I need macro that will automaticaly change range based on data in cells

Thanks it works but i think there is only one detail that is missing

sometimes i get this message ''Solver encountered an error value in the objective cell or a constraint cell'' - i think i get this message because there is also constraint in range AD4:AD? and that constraint also have to change everytime - find last row and modify it in constraint list in solver like ''changing variable cell'' in code above

SolverAdd CellRef:="$AD$4:$AD$10" -> macro has to change that part too by finding last row in range $AD$4:$AD$ ?

How to add this ?

and one question - if i add some more constraint in the future, what is the easiest way to add constraints as code in macro ? I'm asking this because your code is quite different from the one I got while I recorded a macro


sorry because model is in my language, but this is large file and translation everything would be quite challenge. In column T i have plant name and in columns besides there are some economic values. In column AB there are associated botanic family and one part of optimization in AB section is about limitation of total ussage of certain botanic families ex. Brassicaceae

SolverAdd CellRef:="$AD$4:$AD$10" -> macro has to change that part too by finding last row in range $AD$4:$AD$ ?

Use the same technique as I used in the macro to set the X4:X?? range and use another letter say j to be dimmed as an Integer. And this constraint needs to be set when the solver model is build by the macro the same way as the range X4:X? is set up

if i add some more constraint in the future, what is the easiest way to add constraints as code in macro ?

Just write it in as a "SolverAdd CellRef:=" line where you add a relation i.e.

SolverAdd CellRef:="$X$4:$X$" & i, Relation:=1, FormulaText:="20"

tells solver that the range X4:X? should be equal to or less 20. Relation:=2 means equal to and Relation:=3 means equal to or greater than a specified value. Relation:=4, FormulaText:="integer" sets the result in that specified range to integers and Relation:=5, FormulaText:="binary" sets the result to the specified range to binary values.

You can also specify a range

SolverAdd CellRef:="$E$12:$G$12", Relation:=1, FormulaText:="$E$11:$G$11"

were the result values in E12:G12 should be equal to or less than the values in E11:G11

Testing the model a bit more I also got the ''Solver encountered an error value in the objective cell or a constraint cell', but part of the problem seems to be some underlying calculation so I pressed F9 to get excel to calculate the workbook and ded a solver rerun and that did work.

What seems to be important is that the range X4:X? should be equal to or less than the T4:T? range if it's greater you do get some funny results in the AD column. Could be fixed in the macro by adding this line.

i = ActiveCell.Row
Range("X" & i + 1 & ":X44").ClearContents

or you do it manually.

sorry because model is in my language,

Don't worry, this was more a bit of a warning that since I do not understand the language I could not work out how things interacted so it could be my macro made a mess of resuts elsewhere as there are quite a number of macros in your uploaded file.

Alf

Thank you for explanation. it helps me to understand how this works

What seems to be important is that the range X4:X? should be equal to or less than the T4:T? range if it's greater you do get some funny results in the AD column. Could be fixed in the macro by adding this line.

Yes, whole range from column T:Z have same number of rows. allways

Dim i As Integer
Dim j As Integer

Cells(Application.Evaluate("MAX(IF(T4:T45<>"""",ROW(T4:T45)),0,1)"), "T").Select
i = ActiveCell.Row

Cells(Application.Evaluate("MAX(IF(AB4:AB45<>"""",ROW(AB4:AB45)),0,1)"), "AB").Select
j = ActiveCell.Row

SolverReset

SolverOk SetCell:="$AB$3", MaxMinVal:=1, ValueOf:=0, ByChange:="$X$4:$X$" & i, Engine:=1
SolverAdd CellRef:="$AD$4:$AD$10", Relation:=1, FormulaText:="20"
SolverAdd CellRef:="$W$45", Relation:=2, FormulaText:="$C$5"
SolverAdd CellRef:="$X$4:$X$" & i, Relation:=1, FormulaText:="20"
SolverAdd CellRef:="$X$4:$X$" & i, Relation:=3, FormulaText:="5"
SolverSolve


i = ActiveCell.Row
Range("X" & i + 1 & ":X44").ClearContents
End Sub

This is what i have right now, but still doesent work quite i imagined. Sometimes it leave last few rows blank ? But it works when I manually adjust in solver menu.

I think the main problem is with AD4:AD ? If macro could adjust last row in the solver constraint, then it would work perfectly. Same thing as in X column

Your macro is not right i.e. you need to change the order of a line and having calculated a value for j it should be used in the AD constraint. Not sure why you used the AB rang in the calculation of j.

I changed that to AD but if that's wrong you change it back to AB.

Dim i As Integer
Dim j As Integer

Cells(Application.Evaluate("MAX(IF(T4:T45<>"""",ROW(T4:T45)),0,1)"), "T").Select
i = ActiveCell.Row

Cells(Application.Evaluate("MAX(IF(AD4:AD45<>"""",ROW(AD4:AD45)),0,1)"), "AD").Select
j = ActiveCell.Row

Range("X" & i + 1 & ":X44").ClearContents

SolverReset

SolverOk SetCell:="$AB$3", MaxMinVal:=1, ValueOf:=0, ByChange:="$X$4:$X$" & i, Engine:=1
SolverAdd CellRef:="$AD$4:$AD$ & j", Relation:=1, FormulaText:="20"
SolverAdd CellRef:="$W$45", Relation:=2, FormulaText:="$C$5"
SolverAdd CellRef:="$X$4:$X$" & i, Relation:=1, FormulaText:="20"
SolverAdd CellRef:="$X$4:$X$" & i, Relation:=3, FormulaText:="5"
SolverSolve

End Sub

Alf

Thank you so much. This is it

You are welcome.

Thanks for feedback and rep

Alf