Help need to fully autmate What If Analysis

[rv02]

Hi Everyone

I was wondering whether you can help me to write a VBA code. I have a code that performs What If analysis (I did the record Macro to get the code). The code is below

Please Login or Register  to view this content.

What I need to do (if possible to) is to write the code to do the following but my VBA is not strong enough. I have attached the sample spreadsheet to illustrate what I need to do:

When a user runs the Macro for the following to happen:
A form to appear for the user to select the following

Select a division (from choice of what id in the sheet as a list)
Set the Actual Score need to reach 92% to be the same as Actual score to date (for example b17 to have the same value of B11)
Set the target (depending on the user's choice)

Then to perform the What IF for the Highlighted cells (yellow) by changing Row 16 and 17 depending on the division chosen.

The example is
User chooses Division BB and Sets target to be 92%. (B17 =0.92 by changing B16 to get to the target)
Then the WhatIF code above should do perform the following
a) Set B15 to be the same value as B11
b) perform the what if Analysis above on cells B16&B17 by changing cells B16 to reach the chosen target in B17.

The remainder of the values in each column is calculated by formulae except the grey shaded areas which I will enter manually every week.

I hope this makes sense as to what I need to achieve and any help is appreciated

[JapanDave]

I don't follow?

[rv02]

What I need is to do the following:

The spreadsheet is already set up with the appropriate formulas, I want the user to be able to choose his or her target and division for the What IF analysis to be carried out so:

Once the macro button (which I will put on the sheet is pressed) the user will be presented with a input form to enter their division (from a list file drop down box) and set their target.

For example user can choose division CC and target .94 then, cell H16 should have whatever value is in H11 and the what if analysis will be performed based on Goal Seek by changing cell H16 to get to target (h17) which is 0.94.

Hope this is a bit clearer now

Then the what if ana;ysis will be performed (like the code attached) b