Solver VBA Loop Export to New Sheet

Question

Hi, all! I am asking for some help here. I am a demand planner and have created a holts-winters forecasting model in excel for each of the SKUs I manage. I have been developing a MACRO button for when pressed, solver optimizes MAPE (accuracy metric) by changing cells for alpha, beta, and gamma coefficients. The cells that are changed by solver are then connected to formulas to carry out holts-winters to make a prediction for sales of the next 12 months of that product. Finally, the SKU reviewed and the calculated next 12 month predictions are copied onto a newly created sheet. This is created in a loop for each SKU in the file until there is no more remaining. That macro works and successfully transfers all intended forecasts but for some reason the coefficients being changed by solver are not being changed to the optimal values in their designated cells prior to the forecasts being made, resulting in un-optimal forecasts being made and copied to the new sheet. For example, if I run solver manually for each SKU, the coefficients are altered, which directly influence the forecasting formulas. When the MACRO code is ran, the forecasting values calculated from the same coefficients being changed and same cell being optimized do not align. I know the MACRO code is incorrect. I have tried ensuring solver reset within the loop and even had the code run solver with the exact requirements already in the solver add-on when ran manually without success. Any help would be greatly appreciated! I am just looking for the output from the MACRO code to match the output when solver is ran manually, as this would automate the process. Thanks in advance!

Answer 1

It is far too complicated to recreate such a scenario. And if it does not match yours, then our solution will not work for you.

For this kind of requests, please create a sample file with the layout of your original file, filled with sample data and colored cells with the expected result.

At best make a copy of your original file and anonymize the necessary data. For this please download this file
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1
Open your Excel file
Right-click on the sheet tab
Choose "View Code"
Press CTRL-M
Select the downloaded file and import
Close the VBA editor
Select the cells with the confidential data
Press Alt-F8
Choose the macro Anonymize
Click Run

Upload it on OneDrive (or an other Online File Hoster of your choice) and post the download link here.
https://support.office.com/en-us/article/Share-OneDrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.