Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I replace forumulas with the round function in one go
I am constantly working on spreadsheets and then realise I wish I had entered
the "round" function at the start. Instinctively I thought of Ctrl+H to replace but can't figure out how you would put a ,0) at the end. Also considered a macro with typing - F2, Home, =Round(,End, ,0) - but dont know how to get this to work over a large range of formulas. Any help would be appreciated |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I replace forumulas with the round function in one go
I would suggest using the Precision as Displayed option available under
Tools, Options, Calculation. Make sure that all cells have the proper number format before you set this workbook level setting. That is, if you want a cell's value rounded to 2 decimal places for example, make sure that that is how it is number formatted. -- Jim "GusDubs" wrote in message ... |I am constantly working on spreadsheets and then realise I wish I had entered | the "round" function at the start. Instinctively I thought of Ctrl+H to | replace but can't figure out how you would put a ,0) at the end. | Also considered a macro with typing - F2, Home, =Round(,End, ,0) - but dont | know how to get this to work over a large range of formulas. | Any help would be appreciated |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I replace forumulas with the round function in one go
... then realise I wish I had entered the "round" function
Also considered a macro ...but don't know how to get this to work over a large range of formulas. I have a toolbar button that runs the following code. Adjust if you always want it to round to 0. Sub Round_Add() '// = = = = = = = = = = = = = = = = = = = = = = = = = = = '// Adds =ROUND( ) to Formulas in current Selection '// May have more than one area selected '// Avoids adding Round to beginning if already used '// = = = = = = = = = = = = = = = = = = = = = = = = = = = Dim BigRng As Range Dim Rng As Range Dim Cell As Range Dim Equ As String Dim iRound As Integer On Error Resume Next If Not (TypeOf Selection Is Range) Then Exit Sub Set BigRng = Selection.SpecialCells(xlFormulas) If BigRng Is Nothing Then Exit Sub iRound = InputBox("Round to how many digits?", , 2) Equ = "=Round(#,n_)" Equ = Replace(Equ, "n_", iRound) For Each Rng In BigRng.Areas For Each Cell In Rng.Cells If Not Cell.Formula Like "=ROUND(*" Then Cell.Formula = Replace(Equ, "#", Mid$(Cell.Formula, 2)) End If Next Cell Next Rng End Sub -- HTH. :) Dana DeLouis Windows XP, Office 2003 "GusDubs" wrote in message ... I am constantly working on spreadsheets and then realise I wish I had entered the "round" function at the start. Instinctively I thought of Ctrl+H to replace but can't figure out how you would put a ,0) at the end. Also considered a macro with typing - F2, Home, =Round(,End, ,0) - but dont know how to get this to work over a large range of formulas. Any help would be appreciated |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I replace forumulas with the round function in one go
Dana
That is perfect - Thank you very much Cheers GusDubs "Dana DeLouis" wrote: ... then realise I wish I had entered the "round" function Also considered a macro ...but don't know how to get this to work over a large range of formulas. I have a toolbar button that runs the following code. Adjust if you always want it to round to 0. Sub Round_Add() '// = = = = = = = = = = = = = = = = = = = = = = = = = = = '// Adds =ROUND( ) to Formulas in current Selection '// May have more than one area selected '// Avoids adding Round to beginning if already used '// = = = = = = = = = = = = = = = = = = = = = = = = = = = Dim BigRng As Range Dim Rng As Range Dim Cell As Range Dim Equ As String Dim iRound As Integer On Error Resume Next If Not (TypeOf Selection Is Range) Then Exit Sub Set BigRng = Selection.SpecialCells(xlFormulas) If BigRng Is Nothing Then Exit Sub iRound = InputBox("Round to how many digits?", , 2) Equ = "=Round(#,n_)" Equ = Replace(Equ, "n_", iRound) For Each Rng In BigRng.Areas For Each Cell In Rng.Cells If Not Cell.Formula Like "=ROUND(*" Then Cell.Formula = Replace(Equ, "#", Mid$(Cell.Formula, 2)) End If Next Cell Next Rng End Sub -- HTH. :) Dana DeLouis Windows XP, Office 2003 "GusDubs" wrote in message ... I am constantly working on spreadsheets and then realise I wish I had entered the "round" function at the start. Instinctively I thought of Ctrl+H to replace but can't figure out how you would put a ,0) at the end. Also considered a macro with typing - F2, Home, =Round(,End, ,0) - but dont know how to get this to work over a large range of formulas. Any help would be appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
numerical integration | Excel Discussion (Misc queries) | |||
Replace function | Excel Discussion (Misc queries) | |||
The ROUND function | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |