Macro to calculate range by a percentage

**judasdac** · 06-26-2009, 04:21 PM

Hi All,

I'm trying to do a forecast of my organization's budget for the next five years and need to create scenarios where certain figures will either be increased or decreased by a specified percentage that varies from 5% to 20%. I have recorded eight macros that can accomplish this task, but I feel like there must be a more effienct method.

In addition, I would like to increase or decrease the figures from either column H or column J. Column R is the results column. This is the macro I recorded that provides for a 10% decrease:

Please Login or Register  to view this content.

Since I recorded it, the macro is kind of hacky :-) Any help would be greatly appreciated!

Cheers,
Tim

**Leith Ross** · 06-26-2009, 04:40 PM

Hello Tim,

I don't know if you have explored using Excel Scenarios for this purpose. It allows you to easily setup "what if" scenarios and test the outcomes. This may be a better option than recording a lot of macros. You can access the Scenario Manager from the menu Tools > Scenarios...

**judasdac** · 06-26-2009, 04:58 PM

Hi Leith,
Thanks for the response. I had considered using the scenario manager, but the range of cells where the values could be increased or decreased is 185 (R5:R190). Also, I have each fiscal year set up as a separate workbooks with 14 worksheets (12 months, beginning, and end) so I think macros would be more flexible.

I would attach the file before we are blocked from uploading files. Ideally, I would be able to select either column H or J and then choose the percentage to increase or decrease those figures by.

**mudraker** · 06-26-2009, 05:23 PM

Tim

You sya the posted macro code works perfectly & that you want the results in column R

Does not your macro place the formula & result in column Q

Try this macro
It asks the user toinput a percentage value
e.g an entry10 = 10%
It asks the user to select the data column
If column H or J or any cell in those 2 columns are selected It places the formula in column R

Please Login or Register  to view this content.

**judasdac** · 06-26-2009, 05:53 PM

Perfect!! Thanks so muck Leith and Mudraker.

The best part is that I actually understand this solution and should be able to replicate it in different situations.

Learning something new is a great way to end the work week :-)

**mudraker** · 06-26-2009, 06:11 PM

Darn, I did not make the code complex enough

Now we have another member going to building & modifying their own macros

Actually its good to see that you understand it & can updapt it for other uses
Happy programming

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save