Hi everyone, this is my first post.
Thanks in advance to anyone for their time and help.
I'm currently working on a minimum variance portfolio optimisation task that needs to re-balance the portfolio every week, based on new weights that are calculated through running solver.
I must repeat the process 345 times so an automated solver macro would be very helpful.
The code I've written currently runs but I receive the Solver error message " Error in Model. Please verify that all cells and constraints are valid." In addition, the cell I have set to change to Min (the cell containing the variance) remains the same after running it.
I have also tried creating a for loop however this also did not work, and after reconsidering I actually prefer being able to run the macro for specific cells instead of the entire data set.
This is the current version of the code:
Sub Macro1() ' Keyboard Shortcut: Ctrl+y Application.Calculation = xlCalculationAutomatic ThisWorkbook.Worksheets(Sheet1).Activate SolverReset SolverOk SetCell:=ActiveCell, MaxMinVal:=2, ValueOf:=0, ByChange:="ActiveCell.Offset(0,-6) : ActiveCell.Offset(0,-2)", Engine:=1, EngineDe[attach=1228752][/attach]c:="GRG Nonlinear" SolverAdd CellRef:="ActiveCell.Offset(0,-1)", Relation:=2, FormulaText:="1" SolverAdd CellRef:="ActiveCell.Offset(0,-6):ActiveCell.Offset(0,-2)", Relation:=3, FormulaText:="0" SolverSolve ActiveCell.Offset(5, 0).Select Application.Calculation = xlCalculationAutomatic End Sub
I've attached a sample document of the one I'm currently working on as a reference.
If anyone knows how I can get the Macro running properly it would be a massive help.