On Saturday, January 5, 2013 9:24:21 AM UTC-8, joeu2004 wrote:
"joeu2004" wrote:

Sub RoundSelection()

Const pctChange As Double = 0.07 ' for 7%

Dim Cell As Range

For Each Cell In Selection

Cell = WorksheetFunction.Round(Cell*(1+pctChange), 2)

Next Cell

End Sub



If you would like the flexibility of putting the percentage change into a

cell, obviating the need to alter the macro, you could write:



Sub roundSelection()

Dim pctChange As Double

Dim Cell As Range

pctChange = Range("pctChange")

For Each Cell In Selection

Cell = WorksheetFunction.Round(Cell * (1 + pctChange), 2)

Next Cell

End Sub



To use the macro:



1. Enter 7% into some cell. (Enter -7% for a percentage decrease.)

2. With that cell selected, enter pctChange into the Name Box (upper left).

3. Select the data to be modified.

4. Press alt+F8 and run the macro roundSelection.

5. You can now delete the value in the cell used for #1.



Note: If you have already done this once and you want to use a different

cell for #1, you should use the Name Manager to delete or alter the previous

"refers to" for the name pctChange. For Excel 2007, click on the Formula

tab, then Name Manager.

I like...!!!
Not a single .Select in the whole code. Off to my archives with this one as a nice little package for reference.

Regards,
Howard