"tb" wrote:
Yes my data is constant. Would you do me a favor and show me how
I should amend the VBA script which was suggested in the Microsoft
Community forum so that it also applies the percentage increase?
Sub RoundSelection()
Dim Cell As Range
For Each Cell In Selection
Cell.Value = WorksheetFunction.Round(Cell.Value, 2)
Next Cell
End Sub

Be sure to make a backup copy of the Excel file before trying anything.

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

Change Round to Rounddown to truncate, or to Roundup to round up.

Note: Use WorksheetFunction.Round as shown, not the VBA Round function.
The latter rounds differently ("banker's rounding").

"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.

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

On 1/5/2013 at 11:24:21 AM joeu2004 wrote:


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.

So... In order to save this macro in my workbook do I have to save the
file with the extension .xlsm instead of .xlsx? Am I right?

The reason I am asking is because these instructions do not say
anything about that:
<http://office.microsoft.com/en-001/excel-help/create-or-delete-a-macro-HP010014111.aspx

--
tb

That is correct.

Gord


On Wed, 3 Dec 2014 21:58:25 +0000 (UTC), "tb"
wrote:

So... In order to save this macro in my workbook do I have to save the
file with the extension .xlsm instead of .xlsx? Am I right?