Writing a macro that remembers scenarios and plots the numbers in a graph

[MrGeek1]

Hi all,

I'd like to have your help on how to build a macro that reads the different scenarios based on a data validation list. Can you provide me with any guidance in order to move forward? I've tried recording a macro but it does not read the change in the list when I click the drop down menu and a different scenario.

Thanks in advance!

 

[Rijnsent]

Hi MrGeek,
If I understand your question correctly, you have one cell that has a validation list. You change the value of that cell and your whole sheet/workbook calculates and gives a certain outcome? And you want a macro to change the value of that cell, calculate the whole workbook and copy-paste the answers of a certain output range to a e.g. table format? Do elaborate a bit and if you have that recorded code, please post it here too (in CODE brackets).
Thanks,
Koen

 

[MrGeek1]

Hi MrGeek,
If I understand your question correctly, you have one cell that has a validation list. You change the value of that cell and your whole sheet/workbook calculates and gives a certain outcome? And you want a macro to change the value of that cell, calculate the whole workbook and copy-paste the answers of a certain output range to a e.g. table format? Do elaborate a bit and if you have that recorded code, please post it here too (in CODE brackets).
Thanks,
Koen

Hi Rijnsent,
What you describe is correct. I don't have any code written for this, because I like to have some first guidelines before writing a code. Do you have any suggestions?

 

[Rijnsent]

Hi MrGeek,
as step 1 you could do a course, e.g. Free VBA Training Course or Excel VBA Programming - a free course for complete beginners so you have a foundation.
Step 2 you could try coming up with some pseudo code. Basically, describe the logic your program needs to follow e.g.:

Sub CalculateScenarios()

'change value in sheet "Main", cell C5 to the next value in the list
'calculate workbook
'copy Worksheet "Outcome", range B5:B10 to Worksheet "Results", starting in C2, every time the column next to it.

End Sub

As step 3, do that step 2 and record the macro while you are doing it manually. The code that is generated really helps in writing the code you want to write.
Step 4: starting from the pseudo code in step 2, start writing real code with the help of stuff you recorded.
Step 4b: If you get stuck, post your code e.g. on this forum (in CODE brackets) and do describe you issue quite extensive, as others are pretty bad at mind-reading .
Hope that gets you started,
Koen