Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Late Rounding of Formula
Is there (or can there be) a macro or a function that changes a formula like
=C1*$R4 in =round(C1*$R4,0) etc. I have many spreadsheets with hundreds of formulas on each sheet that need to be rounded. I would have to change individually every single formula. Is there an easier way? It could be the "Replace" function if it worked with the possibility to change the formula on both ends at the same time such as (example from above): Replace: = With: =round( [####=Joker] ,0) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Late Rounding of Formula
Possibly a little cludgy but this code snippet should work
for each cell in range(whatever) cell.Formula = "=round(" & Right(cell.Formula, Len(cell.Formula) - 1) & ",2)" next |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Late Rounding of Formula
Building on the suggestion already posted, you could loop through each cell
with a formula that evaluates to a number in each worksheet and change the formula as suggested. Or, if you want to select the formulas that are modified (which gives you more control over what cells get changed), change Aidan's suggestion to For each cell in selection ..... Just be sure to back up your work. Sub Macro2() Dim rngFormulas As Range Dim rngCell As Range Dim wksSheet As Worksheet On Error Resume Next For Each wksSheet In Worksheets Set rngFormulas = Nothing Set rngFormulas = wksSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) If Not rngFormulas Is Nothing Then For Each rngCell In rngFormulas If LCase(Left(rngCell.Formula, 6)) < "=round" Then rngCell.Formula = "=round(" & Right(rngCell.Formula, _ Len(rngCell.Formula) - 1) & ",2)" End If Next rngCell End If Next wksSheet "fak119" wrote: Is there (or can there be) a macro or a function that changes a formula like =C1*$R4 in =round(C1*$R4,0) etc. I have many spreadsheets with hundreds of formulas on each sheet that need to be rounded. I would have to change individually every single formula. Is there an easier way? It could be the "Replace" function if it worked with the possibility to change the formula on both ends at the same time such as (example from above): Replace: = With: =round( [####=Joker] ,0) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
How I need correct formula, that no t rounding? | Excel Worksheet Functions | |||
Rounding formula won't copy to other cells in column - why? | Excel Discussion (Misc queries) | |||
How do I get my formula to stop rounding up | Excel Worksheet Functions |