Round Function

  • Thread starter Thread starter Guest
  • Start date Start date
You could use a help sheet, I doubt you want to use round in all 16777216 cells
assume you have values in A1:H500 in Sheet1, insert a new sheet and select
A1:H500, with A1 as the active cell type =Sheet1A1
press ctrl + enter, now you can copy and paste special as values over the
old values in Sheet1 or paste as values in place and if you want you can
delete Sheet1 and rename the help sheet to Sheet1


Regards,

Peo Sjoblom
 
One way:

Public Sub RoundEverything()
Const sWRAPPER As String = "=Round(#, 2)"
Dim rCell As Range
Dim rReplace As Range
On Error Resume Next
Set rReplace = Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)
If rReplace Is Nothing Then
Set rReplace = Cells.SpecialCells( _
xlCellTypeConstants, xlNumbers)
Else
Set rReplace = Union(rReplace, _
Cells.SpecialCells(xlCellTypeConstants, xlNumbers))
End If
On Error GoTo 0
If Not rReplace Is Nothing Then
For Each rCell In rReplace
With rCell
.Formula = Replace(Replace( _
,sWRAPPER "#", .Formula), "(=", "(")
End With
Next rCell
End If
End Sub
 
Thanks a lot!

"Peo Sjoblom" escreveu:
You could use a help sheet, I doubt you want to use round in all 16777216 cells
assume you have values in A1:H500 in Sheet1, insert a new sheet and select
A1:H500, with A1 as the active cell type =Sheet1A1
press ctrl + enter, now you can copy and paste special as values over the
old values in Sheet1 or paste as values in place and if you want you can
delete Sheet1 and rename the help sheet to Sheet1


Regards,

Peo Sjoblom
Click to expand...
 
Back
Top