Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default applying rounding formula to entire worksheet

Hi, I have worksheet full of prices that I want to round to the nearest
multiple of 10 then subtract 1 to get a nice price with 9 on the end. (having
already multiplied the original numbers by a percent to change currencies)

I know how to make $47 into $49 by using =mround(47,10)-1 but how do I
apply it to the whole sheet? Paste special Formulas is not working for me...

Thanks
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: applying rounding formula to entire worksheet

To apply the rounding formula to the entire worksheet:
  1. Select the cell with the formula you want to apply to the entire worksheet.
  2. Click on the small square in the bottom right corner of the selected cell. This will activate the "Fill" feature.
  3. Drag the mouse down to the last cell in the column that you want to apply the formula to. This will fill the formula down to all the cells in that column.
  4. If you want to apply the formula to other columns in the worksheet, select the entire column by clicking on the column letter at the top of the worksheet, then repeat steps 2 and 3.

To apply the formula to a specific range of cells using the "Name Manager":

1. Click on the "Formulas" tab in the Excel ribbon.
2. Click on the "Name Manager" button in the "Defined Names" group.
3. Click on the "New" button to create a new named range.
4. Enter a name for the range (e.g. "RoundedPrices").
5. In the "Refers to" field, enter the formula you want to apply to the range, using absolute cell references (e.g. =MROUND($A$1,10)-1).
6. Click "OK" to save the named range.
7. Select the range of cells you want to apply the formula to.
8. Click on the "Formulas" tab in the Excel ribbon.
9. Click on the "Name Manager" button in the "Defined Names" group.
10. Select the named range you created in step 4.
11. Click on the "Edit" button.
12. In the "Refers to" field, change the cell reference to the top-left cell of the range you selected in step 7 (e.g. =MROUND($A$1,10)-1).
13. Click "OK" to save the changes.
14. The formula should now be applied to the selected range of cells.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 329
Default applying rounding formula to entire worksheet

Hi mateo,

You can do it with a macro like the following, which will add a formula to any
selected cells, regardless of whether they hold formulae or values. Just be
careful not to run it against cells containing text!

Sub RoundDownRange()
Dim OldVal As Object
Dim NewVal As String
For Each OldVal In Selection
NewVal = OldVal.Formula
If Left(NewVal, 1) = "=" Then
NewVal = Right(NewVal, Len(NewVal) - 1)
End If
NewVal = "=ROUND(" & NewVal & ",-1)-1"
OldVal.Formula = NewVal
Next OldVal
End Sub

Cheers

--
macropod
[MVP - Microsoft Word]


"mateo" wrote in message
...
Hi, I have worksheet full of prices that I want to round to the nearest
multiple of 10 then subtract 1 to get a nice price with 9 on the end. (having
already multiplied the original numbers by a percent to change currencies)

I know how to make $47 into $49 by using =mround(47,10)-1 but how do I
apply it to the whole sheet? Paste special Formulas is not working for me...

Thanks



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 340
Default applying rounding formula to entire worksheet

To round all numbers to nearest 10:

Select tools calculation Precision as displayed then format cells
number custom "#,.00".


(Switch ",." to ".," for European settings). It's probably best to copy
data to a new workbook first and then paste back as formulas only.

To subtract 1 from cells:

Enter 1 in a cell copy, edit goto special constants numbers and
Edit paste Special Values and Subtract.

mateo wrote:

Hi, I have worksheet full of prices that I want to round to the nearest
multiple of 10 then subtract 1 to get a nice price with 9 on the end. (having
already multiplied the original numbers by a percent to change currencies)

I know how to make $47 into $49 by using =mround(47,10)-1 but how do I
apply it to the whole sheet? Paste special Formulas is not working for me...

Thanks


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 694
Default applying rounding formula to entire worksheet

Another way to do it is to insert a new worksheet and use the formula
referencing the cells on the original worksheet and then copy and paste
special values back to the original worksheet.
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Lori" wrote:

To round all numbers to nearest 10:

Select tools calculation Precision as displayed then format cells
number custom "#,.00".


(Switch ",." to ".," for European settings). It's probably best to copy
data to a new workbook first and then paste back as formulas only.

To subtract 1 from cells:

Enter 1 in a cell copy, edit goto special constants numbers and
Edit paste Special Values and Subtract.

mateo wrote:

Hi, I have worksheet full of prices that I want to round to the nearest
multiple of 10 then subtract 1 to get a nice price with 9 on the end. (having
already multiplied the original numbers by a percent to change currencies)

I know how to make $47 into $49 by using =mround(47,10)-1 but how do I
apply it to the whole sheet? Paste special Formulas is not working for me...

Thanks





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default applying rounding formula to entire worksheet

This works great but is too tedious because of need to select each price
group separately to avoid text.

"macropod" wrote:

Hi mateo,

You can do it with a macro like the following, which will add a formula to any
selected cells, regardless of whether they hold formulae or values. Just be
careful not to run it against cells containing text!

Sub RoundDownRange()
Dim OldVal As Object
Dim NewVal As String
For Each OldVal In Selection
NewVal = OldVal.Formula
If Left(NewVal, 1) = "=" Then
NewVal = Right(NewVal, Len(NewVal) - 1)
End If
NewVal = "=ROUND(" & NewVal & ",-1)-1"
OldVal.Formula = NewVal
Next OldVal
End Sub

Cheers

--
macropod
[MVP - Microsoft Word]


"mateo" wrote in message
...
Hi, I have worksheet full of prices that I want to round to the nearest
multiple of 10 then subtract 1 to get a nice price with 9 on the end. (having
already multiplied the original numbers by a percent to change currencies)

I know how to make $47 into $49 by using =mround(47,10)-1 but how do I
apply it to the whole sheet? Paste special Formulas is not working for me...

Thanks




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default applying rounding formula to entire worksheet

hi, thanks.

first, you must be using a different version of excel than me because "tools
calculation" doesn't exist on my excel 2003. If I highlight on a cell then

right click, I can choose Format cells. When I go to custom and add "#,.00"
all it does is output a value with two decimal places. -- not round to the
nearest 10. please advise.



"Lori" wrote:

To round all numbers to nearest 10:

Select tools calculation Precision as displayed then format cells
number custom "#,.00".


(Switch ",." to ".," for European settings). It's probably best to copy
data to a new workbook first and then paste back as formulas only.

To subtract 1 from cells:

Enter 1 in a cell copy, edit goto special constants numbers and
Edit paste Special Values and Subtract.

mateo wrote:

Hi, I have worksheet full of prices that I want to round to the nearest
multiple of 10 then subtract 1 to get a nice price with 9 on the end. (having
already multiplied the original numbers by a percent to change currencies)

I know how to make $47 into $49 by using =mround(47,10)-1 but how do I
apply it to the whole sheet? Paste special Formulas is not working for me...

Thanks



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 329
Default applying rounding formula to entire worksheet

Hi mateo,

Had you mentioned there being an issue with text & values being interspersed, I might have suggested using:

Sub RoundDownRange()
Dim OldVal As Object
Dim NewVal As String
For Each OldVal In Selection
If Application.WorksheetFunction.IsNumber(OldVal.Valu e) Then
NewVal = OldVal.Formula
If Left(NewVal, 1) = "=" Then
NewVal = Right(NewVal, Len(NewVal) - 1)
End If
NewVal = "=ROUND(" & NewVal & ",-1)-1"
OldVal.Formula = NewVal
End If
Next OldVal
End Sub

Although the code does what you asked for, the results are a bit odd (ie -1) if the starting value is less than 5.

Cheers

--
macropod
[MVP - Microsoft Word]


"mateo" wrote in message ...
| This works great but is too tedious because of need to select each price
| group separately to avoid text.
|
| "macropod" wrote:
|
| Hi mateo,
|
| You can do it with a macro like the following, which will add a formula to any
| selected cells, regardless of whether they hold formulae or values. Just be
| careful not to run it against cells containing text!
|
| Sub RoundDownRange()
| Dim OldVal As Object
| Dim NewVal As String
| For Each OldVal In Selection
| NewVal = OldVal.Formula
| If Left(NewVal, 1) = "=" Then
| NewVal = Right(NewVal, Len(NewVal) - 1)
| End If
| NewVal = "=ROUND(" & NewVal & ",-1)-1"
| OldVal.Formula = NewVal
| Next OldVal
| End Sub
|
| Cheers
|
| --
| macropod
| [MVP - Microsoft Word]
|
|
| "mateo" wrote in message
| ...
| Hi, I have worksheet full of prices that I want to round to the nearest
| multiple of 10 then subtract 1 to get a nice price with 9 on the end. (having
| already multiplied the original numbers by a percent to change currencies)
|
| I know how to make $47 into $49 by using =mround(47,10)-1 but how do I
| apply it to the whole sheet? Paste special Formulas is not working for me...
|
| Thanks
|
|
|


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 340
Default applying rounding formula to entire worksheet

I was not clear, use tools options and check the precision as
displayed box on the calculation tab you will get a message saying data
will lose accuracy then click OK. You can try this out in a new
workbook and then paste back without formatting.

mateo wrote:

hi, thanks.

first, you must be using a different version of excel than me because "tools
calculation" doesn't exist on my excel 2003. If I highlight on a cell then

right click, I can choose Format cells. When I go to custom and add "#,.00"
all it does is output a value with two decimal places. -- not round to the
nearest 10. please advise.



"Lori" wrote:

To round all numbers to nearest 10:

Select tools calculation Precision as displayed then format cells
number custom "#,.00".


(Switch ",." to ".," for European settings). It's probably best to copy
data to a new workbook first and then paste back as formulas only.

To subtract 1 from cells:

Enter 1 in a cell copy, edit goto special constants numbers and
Edit paste Special Values and Subtract.

mateo wrote:

Hi, I have worksheet full of prices that I want to round to the nearest
multiple of 10 then subtract 1 to get a nice price with 9 on the end. (having
already multiplied the original numbers by a percent to change currencies)

I know how to make $47 into $49 by using =mround(47,10)-1 but how do I
apply it to the whole sheet? Paste special Formulas is not working for me...

Thanks




  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default applying rounding formula to entire worksheet

Or perhaps this revised edition which will change just formulas only.

Sub RoundDownRange()
Dim OldVal As Object
Dim NewVal As String
For Each OldVal In Selection
If OldVal.HasFormula = True Then
If Not OldVal.Formula Like "=ROUND(*" Then
NewVal = OldVal.Formula
If Left(NewVal, 1) = "=" Then
NewVal = Right(NewVal, Len(NewVal) - 1)
End If
NewVal = "=ROUND(" & NewVal & ",-1)-1"
OldVal.Formula = NewVal
End If
End If
Next OldVal
End Sub


Gord Dibben MS Excel MVP

On Wed, 13 Dec 2006 16:13:27 +1100, "macropod" wrote:

Hi mateo,

Had you mentioned there being an issue with text & values being interspersed, I might have suggested using:

Sub RoundDownRange()
Dim OldVal As Object
Dim NewVal As String
For Each OldVal In Selection
If Application.WorksheetFunction.IsNumber(OldVal.Valu e) Then
NewVal = OldVal.Formula
If Left(NewVal, 1) = "=" Then
NewVal = Right(NewVal, Len(NewVal) - 1)
End If
NewVal = "=ROUND(" & NewVal & ",-1)-1"
OldVal.Formula = NewVal
End If
Next OldVal
End Sub

Although the code does what you asked for, the results are a bit odd (ie -1) if the starting value is less than 5.

Cheers




  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default applying rounding formula to entire worksheet

Thanks for that Macropod, I should have mention the text titles. For some
reason when running your new macro after selecting everything it either says
"this macro requires merge cells to be of the same size." Or when just
selecting numbers, it make each row contain the same number as the first cell
in each row.


"macropod" wrote:

Hi mateo,

Had you mentioned there being an issue with text & values being interspersed, I might have suggested using:

Sub RoundDownRange()
Dim OldVal As Object
Dim NewVal As String
For Each OldVal In Selection
If Application.WorksheetFunction.IsNumber(OldVal.Valu e) Then
NewVal = OldVal.Formula
If Left(NewVal, 1) = "=" Then
NewVal = Right(NewVal, Len(NewVal) - 1)
End If
NewVal = "=ROUND(" & NewVal & ",-1)-1"
OldVal.Formula = NewVal
End If
Next OldVal
End Sub

Although the code does what you asked for, the results are a bit odd (ie -1) if the starting value is less than 5.

Cheers

--
macropod
[MVP - Microsoft Word]



  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default applying rounding formula to entire worksheet

After posting my last reply I found the Tools Options feature.

Your method still doesn't work for the task at hand: turning $432 into $429,
$5676 into $5679 etc. If you know how using that simple method, do tell us
please. Thanks.


"Lori" wrote:

I was not clear, use tools options and check the precision as
displayed box on the calculation tab you will get a message saying data
will lose accuracy then click OK. You can try this out in a new
workbook and then paste back without formatting.

mateo wrote:

hi, thanks.

first, you must be using a different version of excel than me because "tools
calculation" doesn't exist on my excel 2003. If I highlight on a cell then

right click, I can choose Format cells. When I go to custom and add "#,.00"
all it does is output a value with two decimal places. -- not round to the
nearest 10. please advise.



"Lori" wrote:

To round all numbers to nearest 10:

Select tools calculation Precision as displayed then format cells
number custom "#,.00".

(Switch ",." to ".," for European settings). It's probably best to copy
data to a new workbook first and then paste back as formulas only.

To subtract 1 from cells:

Enter 1 in a cell copy, edit goto special constants numbers and
Edit paste Special Values and Subtract.

mateo wrote:

Hi, I have worksheet full of prices that I want to round to the nearest
multiple of 10 then subtract 1 to get a nice price with 9 on the end. (having
already multiplied the original numbers by a percent to change currencies)

I know how to make $47 into $49 by using =mround(47,10)-1 but how do I
apply it to the whole sheet? Paste special Formulas is not working for me...

Thanks




  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default applying rounding formula to entire worksheet

Thanks but it didn't work. I also should have mentioned that we are not
working with formulas as I changed currencies by paste special multiply and
then formated cells to have 0 decimals.

If you care to write one that can start from the very beginning in one step
with a bunch of text thrown into the tables, that would be great.

an example: I need to change $4699CAD into $3099Euro using the conversion
rate of 0.66. A macro that could easily adapt to different currencies would
be great.



"Gord Dibben" wrote:

Or perhaps this revised edition which will change just formulas only.

Sub RoundDownRange()
Dim OldVal As Object
Dim NewVal As String
For Each OldVal In Selection
If OldVal.HasFormula = True Then
If Not OldVal.Formula Like "=ROUND(*" Then
NewVal = OldVal.Formula
If Left(NewVal, 1) = "=" Then
NewVal = Right(NewVal, Len(NewVal) - 1)
End If
NewVal = "=ROUND(" & NewVal & ",-1)-1"
OldVal.Formula = NewVal
End If
End If
Next OldVal
End Sub


Gord Dibben MS Excel MVP

On Wed, 13 Dec 2006 16:13:27 +1100, "macropod" wrote:

Hi mateo,

Had you mentioned there being an issue with text & values being interspersed, I might have suggested using:

Sub RoundDownRange()
Dim OldVal As Object
Dim NewVal As String
For Each OldVal In Selection
If Application.WorksheetFunction.IsNumber(OldVal.Valu e) Then
NewVal = OldVal.Formula
If Left(NewVal, 1) = "=" Then
NewVal = Right(NewVal, Len(NewVal) - 1)
End If
NewVal = "=ROUND(" & NewVal & ",-1)-1"
OldVal.Formula = NewVal
End If
Next OldVal
End Sub

Although the code does what you asked for, the results are a bit odd (ie -1) if the starting value is less than 5.

Cheers



  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 329
Default applying rounding formula to entire worksheet

Hi mateo,

It doesn't behave that way for me. On my system, it processes all cells containing only numeric values, or formulae that return
numeric values, and skips any other cells (eg empty cells and cells with text). Merged cells have no effect - the code processes
them just the same as it does with un-merged cells.

Did you modify the code in any way?

Cheers

--
macropod
[MVP - Microsoft Word]


"mateo" wrote in message ...
| Thanks for that Macropod, I should have mention the text titles. For some
| reason when running your new macro after selecting everything it either says
| "this macro requires merge cells to be of the same size." Or when just
| selecting numbers, it make each row contain the same number as the first cell
| in each row.
|
|
| "macropod" wrote:
|
| Hi mateo,
|
| Had you mentioned there being an issue with text & values being interspersed, I might have suggested using:
|
| Sub RoundDownRange()
| Dim OldVal As Object
| Dim NewVal As String
| For Each OldVal In Selection
| If Application.WorksheetFunction.IsNumber(OldVal.Valu e) Then
| NewVal = OldVal.Formula
| If Left(NewVal, 1) = "=" Then
| NewVal = Right(NewVal, Len(NewVal) - 1)
| End If
| NewVal = "=ROUND(" & NewVal & ",-1)-1"
| OldVal.Formula = NewVal
| End If
| Next OldVal
| End Sub
|
| Although the code does what you asked for, the results are a bit odd (ie -1) if the starting value is less than 5.
|
| Cheers
|
| --
| macropod
| [MVP - Microsoft Word]
|
|
|


  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default applying rounding formula to entire worksheet

No, did not modify. Tried again multiple times, the original still works fine
though, and thinking about how I will be referencing each table individually
when going back and forth from the destination webpage, I realize that
rounding each table individually using your original formula is not a problem
at all. Thanks Macropod.

If you care to write one that can start from the very beginning in one step
allowing for a bunch of text thrown into the tables, that would be great.
Converting currencies and then rounding to the nearest nine.

an example: I need to change $4699CAD into $3099Euro using the conversion
rate of 0.66. A macro that could easily adapt to different currencies by
throwing in the percentile would be great.

Cheers
M



"macropod" wrote:

Hi mateo,

It doesn't behave that way for me. On my system, it processes all cells containing only numeric values, or formulae that return
numeric values, and skips any other cells (eg empty cells and cells with text). Merged cells have no effect - the code processes
them just the same as it does with un-merged cells.

Did you modify the code in any way?

Cheers

--
macropod
[MVP - Microsoft Word]


"mateo" wrote in message ...
| Thanks for that Macropod, I should have mention the text titles. For some
| reason when running your new macro after selecting everything it either says
| "this macro requires merge cells to be of the same size." Or when just
| selecting numbers, it make each row contain the same number as the first cell
| in each row.
|
|




  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 329
Default applying rounding formula to entire worksheet

To process the whole worksheet, you could use something like:

Sub RoundDownSheet()
Dim OldVal As Object
Dim NewVal As String
Dim ConvRate
ConvRate = InputBox("What is the conversion rate?", "Currency Conversion", 1)
On Error GoTo Abort
For Each OldVal In ActiveSheet.UsedRange
If Application.WorksheetFunction.IsNumber(OldVal.Valu e) Then
NewVal = OldVal.Formula
If Left(NewVal, 1) = "=" Then
NewVal = Right(NewVal, Len(NewVal) - 1)
End If
NewVal = "=ROUND(" & NewVal & "*" & ConvRate & ",-1)-1"
OldVal.Formula = NewVal
End If
Next OldVal
Abort:
End Sub

This version includes a prompt for the currency conversion rate. If you don't want to apply the same conversion rate throughout the
worksheet, you'll need to change 'ActiveSheet.UsedRange' to 'Selection' and select the range the macro is to apply to. The new
formula retains both the original value and the conversion rate.

Cheers

--
macropod
[MVP - Microsoft Word]


"mateo" wrote in message ...
| No, did not modify. Tried again multiple times, the original still works fine
| though, and thinking about how I will be referencing each table individually
| when going back and forth from the destination webpage, I realize that
| rounding each table individually using your original formula is not a problem
| at all. Thanks Macropod.
|
| If you care to write one that can start from the very beginning in one step
| allowing for a bunch of text thrown into the tables, that would be great.
| Converting currencies and then rounding to the nearest nine.
|
| an example: I need to change $4699CAD into $3099Euro using the conversion
| rate of 0.66. A macro that could easily adapt to different currencies by
| throwing in the percentile would be great.
|
| Cheers
| M
|
|
|
| "macropod" wrote:
|
| Hi mateo,
|
| It doesn't behave that way for me. On my system, it processes all cells containing only numeric values, or formulae that return
| numeric values, and skips any other cells (eg empty cells and cells with text). Merged cells have no effect - the code processes
| them just the same as it does with un-merged cells.
|
| Did you modify the code in any way?
|
| Cheers
|
| --
| macropod
| [MVP - Microsoft Word]
|
|
| "mateo" wrote in message ...
| | Thanks for that Macropod, I should have mention the text titles. For some
| | reason when running your new macro after selecting everything it either says
| | "this macro requires merge cells to be of the same size." Or when just
| | selecting numbers, it make each row contain the same number as the first cell
| | in each row.
| |
| |
|


  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default changing currencies and applying rounding formula

Wow, it works great. Thank you very much, Macropod! I hope someone else gets
to discover this info someday.

Cheers!
Mateo



"macropod" wrote:

To process the whole worksheet, you could use something like:

Sub RoundDownSheet()
Dim OldVal As Object
Dim NewVal As String
Dim ConvRate
ConvRate = InputBox("What is the conversion rate?", "Currency Conversion", 1)
On Error GoTo Abort
For Each OldVal In ActiveSheet.UsedRange
If Application.WorksheetFunction.IsNumber(OldVal.Valu e) Then
NewVal = OldVal.Formula
If Left(NewVal, 1) = "=" Then
NewVal = Right(NewVal, Len(NewVal) - 1)
End If
NewVal = "=ROUND(" & NewVal & "*" & ConvRate & ",-1)-1"
OldVal.Formula = NewVal
End If
Next OldVal
Abort:
End Sub

This version includes a prompt for the currency conversion rate. If you don't want to apply the same conversion rate throughout the
worksheet, you'll need to change 'ActiveSheet.UsedRange' to 'Selection' and select the range the macro is to apply to. The new
formula retains both the original value and the conversion rate.

Cheers

--
macropod
[MVP - Microsoft Word]


"mateo" wrote in message ...
| No, did not modify. Tried again multiple times, the original still works fine
| though, and thinking about how I will be referencing each table individually
| when going back and forth from the destination webpage, I realize that
| rounding each table individually using your original formula is not a problem
| at all. Thanks Macropod.
|
| If you care to write one that can start from the very beginning in one step
| allowing for a bunch of text thrown into the tables, that would be great.
| Converting currencies and then rounding to the nearest nine.
|
| an example: I need to change $4699CAD into $3099Euro using the conversion
| rate of 0.66. A macro that could easily adapt to different currencies by
| throwing in the percentile would be great.
|
| Cheers
| M
|
|
|
| "macropod" wrote:
|
| Hi mateo,
|
| It doesn't behave that way for me. On my system, it processes all cells containing only numeric values, or formulae that return
| numeric values, and skips any other cells (eg empty cells and cells with text). Merged cells have no effect - the code processes
| them just the same as it does with un-merged cells.
|
| Did you modify the code in any way?
|
| Cheers
|
| --
| macropod
| [MVP - Microsoft Word]
|
|
| "mateo" wrote in message ...
| | Thanks for that Macropod, I should have mention the text titles. For some
| | reason when running your new macro after selecting everything it either says
| | "this macro requires merge cells to be of the same size." Or when just
| | selecting numbers, it make each row contain the same number as the first cell
| | in each row.
| |
| |
|



  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 329
Default changing currencies and applying rounding formula

You're welcome

Cheers

--
macropod
[MVP - Microsoft Word]


"mateo" wrote in message ...
| Wow, it works great. Thank you very much, Macropod! I hope someone else gets
| to discover this info someday.
|
| Cheers!
| Mateo
|
|
|
| "macropod" wrote:
|
| To process the whole worksheet, you could use something like:
|
| Sub RoundDownSheet()
| Dim OldVal As Object
| Dim NewVal As String
| Dim ConvRate
| ConvRate = InputBox("What is the conversion rate?", "Currency Conversion", 1)
| On Error GoTo Abort
| For Each OldVal In ActiveSheet.UsedRange
| If Application.WorksheetFunction.IsNumber(OldVal.Valu e) Then
| NewVal = OldVal.Formula
| If Left(NewVal, 1) = "=" Then
| NewVal = Right(NewVal, Len(NewVal) - 1)
| End If
| NewVal = "=ROUND(" & NewVal & "*" & ConvRate & ",-1)-1"
| OldVal.Formula = NewVal
| End If
| Next OldVal
| Abort:
| End Sub
|
| This version includes a prompt for the currency conversion rate. If you don't want to apply the same conversion rate throughout
the
| worksheet, you'll need to change 'ActiveSheet.UsedRange' to 'Selection' and select the range the macro is to apply to. The new
| formula retains both the original value and the conversion rate.
|
| Cheers
|
| --
| macropod
| [MVP - Microsoft Word]
|
|
| "mateo" wrote in message ...
| | No, did not modify. Tried again multiple times, the original still works fine
| | though, and thinking about how I will be referencing each table individually
| | when going back and forth from the destination webpage, I realize that
| | rounding each table individually using your original formula is not a problem
| | at all. Thanks Macropod.
| |
| | If you care to write one that can start from the very beginning in one step
| | allowing for a bunch of text thrown into the tables, that would be great.
| | Converting currencies and then rounding to the nearest nine.
| |
| | an example: I need to change $4699CAD into $3099Euro using the conversion
| | rate of 0.66. A macro that could easily adapt to different currencies by
| | throwing in the percentile would be great.
| |
| | Cheers
| | M
| |
| |
| |
| | "macropod" wrote:
| |
| | Hi mateo,
| |
| | It doesn't behave that way for me. On my system, it processes all cells containing only numeric values, or formulae that
return
| | numeric values, and skips any other cells (eg empty cells and cells with text). Merged cells have no effect - the code
processes
| | them just the same as it does with un-merged cells.
| |
| | Did you modify the code in any way?
| |
| | Cheers
| |
| | --
| | macropod
| | [MVP - Microsoft Word]
| |
| |
| | "mateo" wrote in message ...
| | | Thanks for that Macropod, I should have mention the text titles. For some
| | | reason when running your new macro after selecting everything it either says
| | | "this macro requires merge cells to be of the same size." Or when just
| | | selecting numbers, it make each row contain the same number as the first cell
| | | in each row.
| | |
| | |
| |
|
|
|


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
Need help applying a formula to a certain area of the worksheet pirouz Excel Worksheet Functions 2 May 28th 06 01:28 AM
Offset formula on different worksheet edwardpestian Excel Worksheet Functions 3 May 6th 06 01:07 PM
How can I use one formula to change case on an entire worksheet? pow835 Excel Worksheet Functions 2 April 4th 06 09:04 PM
Comparing a list to a Calendar worksheet. PatrickL Excel Worksheet Functions 0 August 25th 05 04:21 PM
need a formula to copy entire row to next worksheet aledger Excel Worksheet Functions 5 March 8th 05 12:45 AM


All times are GMT +1. The time now is 09:57 AM.

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

About Us

"It's about Microsoft Excel"