Sensitivity Analysis Macro - Changing a Variables Value and Pasting Result into an array

Hi Guys,

I'm looking for a hand in creating a macro to create a sensitivity array.

What I'm trying to do is change the variable's value (from .8 through to 1.2 as in the sheet), then copy the value of a dependent and paste it into an array to use for graphing. I've attached a simple example for reference. I tried hunting down some examples but most turn off updating, as I am changing a variable to get the value I want I don't think this is effective. I'm very much a novice at VBA so any help would be great. My best idea was a loop that continued until all the array cells had been filled in the row then moved onto the next. With respect to the example something like this.

Set Cell B15=0.8
Copy Cell B12
Paste to Cell D15
Set Cell B15+0.05
Copy Cell B12
Paste to Cell D16
.... repeat until B15=1.2
Set B15=1
Offset function +1 row
Repeat until all variables are complete

Like I said I'm a novice and have only taught myself limited VBA, so more than likely one of the local genius' has a far better way of doing it. The attached is a very simplified example of what I am trying to achieve. In reality the result calculated is a fair bit more complicated an involves many more variables, but it should give you an idea of what I am attempting to achieve.

Any assistance would be greatly appreciated, or if someone could point me in the direction of a better way I'd be very Sensivity Example.xlsx.

No problem.

So in the attached example I have filled out the array by manually changing the variable cells in the array individually, then copying and pasting values from the result cell into the corresponding cell in the array. In this example, as the initial value is only multiplied by each variable, the array values are the same for each row, where as in reality they would be different (being the concept of the analysis to graphically identify the magnitude of impact of any variable on the result).

Hope this makes things clearer.

Thanks for the attempt oeldere, but that's not quite what I'm after. I'm sure I may not be explaining it the best.

I am attempting to see the effect of changing one variable at a time on the resultant value in Cell B12.

That is I want to see what happens when I change 'Variable 1' in Cell B15 (linked to Cell B3) to -20% (so reducing 1 by 20% to 0.8). Then I want to 'record' that value keeping all other variables constant into the Cell D15.

Then I want to change 'Variable 1' again keeping all others constant to -15% (so 0.85%), and continue changing 'Variable 1' until it reaches 1.2

Then I want to return 'Variable 1' to 1 and repeat the process for 'Variable 2' to 'Variable 8' to fill out the entire table.

Normally I do this manually but it is very time consuming, just looking for a smart way of doing things.

Cheers for having at go at it though.

So when we get to changing 'Variable 1' in Cell B15 to 1.20 (representing +20% of the original value, being 1) the resultant value in Cell B12 is 180000 and is then pasted into Cell L15.

Then I need to reset Cell B15 to 1 and continue with this process for all variables.