Round Function
 

Insert round function for all the cells in an Excel Worksheet?

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

"Pedro Serra" wrote:

Insert round function for all the cells in an Excel Worksheet?

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


In article ,
"Pedro Serra" wrote:

Insert round function for all the cells in an Excel Worksheet?

Oops, paste error. Should be

sWRAPPER, "#", .Formula), "(=", "(")


In article ,
JE McGimpsey wrote:

,sWRAPPER "#", .Formula), "(=", "(")

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

"Pedro Serra" wrote:

Insert round function for all the cells in an Excel Worksheet?