I am in big trouble with my Excel sheet. How to choose a format to two decimal for a cell that does not display "point zero zero"? I mean I want my some cell in my Excel sheet should include 2 decimal values. But every time I get a whole number.
I am in big trouble with my Excel sheet. How to choose a format to two decimal for a cell that does not display "point zero zero"? I mean I want my some cell in my Excel sheet should include 2 decimal values. But every time I get a whole number.
It is certainly easier, but if you do not know the answer here is a solution to adapt:
attention, the loop is done on all the cells each time you enter data in the table. If you want to do so only for the current cell, use target:Code:Private Sub Worksheet_Change (ByVal Target As Range) lim = Range("a65000" ).End(xlUp).Row For i = 1 To lim Cells (i, 1). Select Selection.NumberFormat = "0.00" If Round (Selection) = Selection Then Selection.NumberFormat = "0" Next i End Sub
Code:Private Sub Worksheet_Change (ByVal Target As Range) target.Select Selection.NumberFormat = "0.00" If Round (Selection) = Selection Then Selection.NumberFormat = "0" End Sub
Gunilla solution gives the most correct (since no commas).
Nevertheless, it would be nice to apply this function only on cells that have a certain format (style number). Otherwise, if the Excel sheet contains style data, the function will return an error quickly.
Hence my question: how to test the formatting of a cell
If <format then cell = numerical>
... as per Gunilla code
else
exit
end
To encode this macro, in Excel (with your file open), type ALT + F11. A window will open Visual Basic. There in the window of exploration left, you have a tree. Double-click on "Sheet1", a blank window will open as a window.
Simply code as given by Gunilla. Attention, you must either stick the first solution or the second, but not both (or comment either by adding an apostrophe before each line).
Save and close. That is, the macro is applied to each cell that you change.
I tested it and it works very well, except just that the macro does not distinguish between a cell-type number or another. So if you come back for example "Y" in a cell, such as the macro will run on any cell in progress, the macro will return an error every time it returns a non-digital.
There are certainly features of types of conversions to make calculations, but I do not know them and they are certainly less "certain" that directly use the correct types.
Bookmarks