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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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!" Else For i = 1 To nr_zec nr_zec_afis = nr_zec_afis & Replace(i, i, 0) Next If nr_zec = 0 Then nr_zec_afis = nr_zec_afis Else nr_zec_afis = "." & nr_zec_afis End If 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 arie.Copy arie.PasteSpecial xlPasteValues End If Next arie End If End Sub |
Instead of Round., RoundUp. or RoundDown. can be used.
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.
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.
Sursa: StackOverflow.com, Monkidea.com
