200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hello,
First of all, thank you for your time in reading my question. Means a lot.
I would like to automate this idea of manually tweaking. The attached excel contains all detail for simplicity of explanation and for easy understanding.
I tried using solver and goal seek but couldn't acheive what i wanted.
Could you please help me ?
Thank you in advance for your support.
As much as this seems to be a case for Solver I do share your exaspiration with it. In the attached workbook I present a VBA solution of my own. The code is entirely in Sheet1's code sheet. You can copy it from there to the identical location in your project. Adjust the four constants at the top to suit your requirements but bear in mind that ranges A, B and T must be of identical size. Observe that the attached workbook is of XLSM format.
The code takes action when you make a change in H6:H8, and that highlights a logical error in your remarks about G9:H9 ("We will NOT have a scenario of not matching totals."). Obvisouly, when you change the targets the totals will no longer match. However, whether they do or don't is irrelevant to the required adjustments in range B.
Note that my code tries to distribute the adjustments near to a percentage basis - not evenly as Solver does. Either way, the calculation is tricky and I didn't test all possible variations of all possible variations. If you find an error please post here by which route you found it and I shall fix it for you.
