sensitivity analysis using VBA

morning all. first timer here - go easy on me.

I have a workbook with multiple sheets. generally, all my inputs are on one tab. my results are spread over a few tabs. I'd like to create a sensitivity tab that does the following.

the sensitivity tab has say 10 columns. the first 2 columns are inputs. the last 8 columns are results. for clarity, the inputs are actually driving the ss from the 1st sheet;while the numbers here are simply inputs I want to sensitize on. what I'd like to do is have VBA code that takes the inputs in columns 1 and 2, 1st row, puts them in the input tab where they go, then put the results associated with those inputs in columns three through 10 on that first row. next it goes to the 2nd row, takes the 1st two columns (which are inputs), puts those values in the 1st sheet where the actual inputs are, then puts the results associated with those inputs in 2nd row columns 3 through 10.

this would continue until it runs out of input rows.


I realize this may not be clear, so I've attached (I think I've attached anyway) a very simple example where the sensitivity tab is pre-populated with the result I'm looking for.

I'm thinking this can be done with some type of a do loop - but I'm not a programmer. I can; however, look at VBA code and generally see what's going on.

thank you in advance for any help provided.

forum_post.xlsx

for clarity - I've been googling and reading up on the excel forums - and wanted to state that my post is not currently cross posted on any other forum.
also - I'm using excel 2010.

Hi Wingfield,

Welcome to the forum

You're example isn't clear to me as the sensitivity shows just 4 columns and are we to assume that the dates will just be the first of each successive year and, I don't understand how your results are arrived at

xladept,

hello.

>> You're example isn't clear to me as the sensitivity shows just 4 columns and are we to assume that the dates will just be the first of each successive year and, I don't understand how your results are arrived at
yeah, it occurs to me that my description doesn't match the spreadsheet. that said, I only have 4 columns in the example rather than 10; but the intent is still the same. the difference in the ss is that I only made 2 results.

>> I don't understand how your results are arrived at
on the two result tabs, I show how the results are arrived at. they're not real meaningful in terms of example. I mainly provide the ss to show what I was trying to do.

so for example, if you put in the actual input location on the "inputs tab" the date 1/1/2011 as input 1, then put in 5% as input 2 - you will then see on the results1 tab 1266. I've also manually put 1266 in the sensitivity tab. same process for the next inputs I want to sensitize on.

>> are we to assume that the dates will just be the first of each successive year
the dates are whatever is in the sensitivity tab.

make sense?? :>)

hmm. I'm thinking I'm not doing a good job of communicating. I'll give it another go.

i would like to have VBA code that has a loop capability.
(a) on first go round through the loop, it takes the value in cell a2 on the sensitivity tab and puts it in cell c2 on the input tab.
(b) then it takes the value in cell B2 on the sensitivity tab and puts it cell C3 on the input tab.
(c) in so doing, both results1 tab and results 2 tab have some answers in cells C2 results1 tab and C2 results2 tab.
(d) the code then puts these answers as follows. cell C2 results1 tab into cell C2 sensitivity tab, then cell C2 results2 tab into cell D2 sensitivity tab.

(e) then it repeats the exact same process but uses the inputs in the sensitivity tab from cells A3 and B3; thus yielding different answers. those answers are then put from the 2 results tabs into the sensitivity tab into cells C3 and D3.

and so on.

hopefully, I've done a better job?

I guess I see how it's hard to be clear. please let me know if this makes sense; and thank you for all the help.

I know this example is non sensible. I have a very robust financial spreadsheet that I want to do some sensitivity analysis on. right now, I'm manually putting in different inputs, then manually populating a table of results.

Wingfield

Dark,

I've been playing with it a bit and it seems to work. quick question (may not be my last). I have column headings in my sensitivity tab. this causes the macro to not work. if I remove the column headings it seems to work. I "think" your code that is in your if statement is looking for a non blank row and assuming that's the start of the inputs?? can i simply tell it to start in row6, column 1 and go from there, so that I can have column headings??

another one. is there a way to make the macro reset somehow? it seems if I don't click the reset button manually, then it puts the same values in all the rows regardless of different inputs??? or here's another thought. is it possible that because of how much memory I'm using, the macro runs faster than the calculations and is filling the table before the other inputs get a chance to propogate the answers to the ss?? weird.

also, perhaps it's just my machine - but it seems slow? my ss is around 1.4meg, so it's a bit big. not sure if that affects speed??

thank you.

You're welcome! Glad it's resolved. You can now mark this post as solved (Go to Thread Tools at the top).