=round(number,num_digits)
How to add a button to Excel to round numbers:
1. If you do not have a Personal Macro Workbook, create one. To create a personal macro workbook, go to Tools, Macro, Record New Macro. Under the “Store Macro In” drop-down menu, choose “Personal Macro Workbook.” Click OK. A personal macro workbook has now been created.
2. Click the ‘Stop’ button.
3. Press Alt-F11 to open the Visual Basic for Applications editor.
4. If there is no VBA Project Window, press Ctrl-R to open it. In the Project – VBA Project Window, find Personal.xls. Double click on it, then double click on Modules.
5. Double-click on Module1.
6. Paste the following code into the module1 code window.
Sub Round0()
Dim strFormula As String 'strFormula is a String.
For Each Cel In Selection 'Cycle through each selected cell.
strFormula = Cel.FormulaR1C1 'Get the formula from the cell.
If strFormula <> "" Then 'Skip if the cell is blank.
If Left(strFormula, 7) = "=ROUND(" Then 'If it's already rounded.
For x = Len(strFormula) To 1 Step -1 'Cycle backwards through the string.
If Mid(strFormula, x, 1) = "," Then 'To find the comma.
strFormula = Mid(strFormula, 8, x - 8) 'strFormula now equals whatever was in the round function.
Exit For 'Stop cycling through the formula.
End If
Next x
End If
If Left(strFormula, 1) = "=" Then strFormula = Mid(strFormula, 2, Len(strFormula) - 1) 'Get rid of any leading equal signs.
Cel.FormulaR1C1 = "=ROUND(" & strFormula & ",0)" 'Round it to 0 places.
End If
Next Cel 'Go to the next cell in the selection.
End Sub
7. Close the Visual Basic window to go back to Excel.
8. Go to the Tools menu, Customize.
9. Choose Commands, Rearrange Commands.
10. Choose Toolbar
11. Pick a toolbar to add your button to (standard, formatting, or custom).
12. Choose where on the toolbar you want it, and click Add… (it will be added above the current selection)
13. Choose Macros, Custom Menu Item, and click OK.
14. Choose Modify Selection, and change the name to “Round Dollars” (or whatever you want the button to say) Then choose assign macro, and choose the Round0 macro.
15. Repeat from step 12 for the Round4 and UnRound macros.
16. Test the buttons in a blank worksheet. If they work, you’re ready to go.
17. When closing Excel, you will be prompted to save the personal macro workbook. Do so.
This is a useful little macro that allows you to edit the actual formula of a bunch of cells at once. Basically, it pulls out the formula that you have in each cell, and throws the 'round' function around it. I have not yet found a way to do this without writing a macro. I use it all the time. It will leave blank cells alone, as well, so you don't have to be careful about what you're selecting.
This version does not seem include a fix for rounding text. However, I have an Unround macro that will reverse this.
Sub UnRound()
Dim strFormula As String 'strFormula is a string.
For Each Cel In Selection 'Cycle through each cell in the selection.
strFormula = Cel.FormulaR1C1 'Get the formula from the cell.
If Left(strFormula, 7) = "=ROUND(" Then 'If the cell is rounded...
For x = Len(strFormula) To 1 Step -1 'Cycle backwards through the formula
If Mid(strFormula, x, 1) = "," Then 'to find the comma.
strFormula = Mid(strFormula, 8, x - 8) 'strFormula equals the formula that was rounded.
Exit For 'Stop cycling through the formula.
End If
Next x
Cel.FormulaR1C1 = "=" & strFormula 'Formula equals whatever was rounded, with a leading equals sign.
If IsError(Cel.Value) = True Then 'But if it kicks out an error
If CVErr(xlErrName) = Cel.Value Then Cel.FormulaR1C1 = strFormula 'If a name error results, get rid of the leading equals sign. (If you mistakenly round a string, you can unround it.)
End If
End If
Next Cel
End Sub
If anyone knows how to code this better, please let me know so I can make my code the best possible.