If it is desired that, in a sheet, numerical values be formatted and displayed by rounding with a certain number of decimals, the following macro can be created. A function can be created just as well. It is important to remember that the respective cells/area must be selected. If the selection is made for the entire spreadsheet, the duration of the application of the code risks being very long, considering that Excel will “sweep” all the cells of the respective sheet.
Sub fc_rotunj() '
Dim arie As Range, nr_zec As String, nr_zec_afis As String
Dim i As Long
nr_zec = InputBox("Insert the number of decimals " & Chr(13) & " (0 - no decimals, 1 - a decimal etc.)" & _
Chr(13) & Chr(13) & "ATTENTION:" & Chr(13) & " the formatting is applied to the selected number cells.", "Number of decimals", 0)
If StrPtr(nr_zec) = 0 Then
MsgBox "You canceled the operation!"
For i = 1 To nr_zec
nr_zec_afis = nr_zec_afis & Replace(i, i, 0)
If nr_zec = 0 Then
nr_zec_afis = nr_zec_afis
nr_zec_afis = "." & nr_zec_afis
For Each arie In Selection
If IsNumeric(arie) And Not IsEmpty(arie) Then
arie.Value = "=Round(" & arie.Value & "," & nr_zec & ")"
arie.NumberFormat = "#,##0" & nr_zec_afis
After selecting the required cells, the macros form is displayed using the ALT+F8 combination (if the Developer menu is not visible) where you will also find fc_rotunj.
- The Round function rounds a number to a specified number of digits.
- The RoundUp function rounds a number by addition, in the direction from 0, behaving like ROUND, except that it only rounds by addition.
- The RoundDown function rounds a number down, toward 0, behaving like ROUND, except that it only rounds down.