Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old January 5th 13, 06:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 829
Default Percentage Increase

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


  #12   Report Post  
Old January 5th 13, 06:58 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2012
Posts: 168
Default Percentage Increase

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
  #13   Report Post  
Old December 3rd 14, 10:58 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 84
Default Percentage Increase

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
  #14   Report Post  
Old December 4th 14, 08:47 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2011
Posts: 621
Default Percentage Increase

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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Percentage increase Lynne Mawson New Users to Excel 2 March 29th 07 02:02 PM
percentage increase Dave F Excel Discussion (Misc queries) 0 January 19th 07 09:22 PM
increase the value of a cell by a percentage ba Excel Discussion (Misc queries) 2 January 5th 06 11:29 PM
percentage increase bigjeffscv Excel Worksheet Functions 1 September 26th 05 11:50 PM
Percentage increase Unionjack jackson Excel Worksheet Functions 2 February 14th 05 03:42 PM


All times are GMT +1. The time now is 12:08 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017