I have used formating a cell using currency and making the decimal to Zero.
in the cell the numbers are being displayed in whole numbers but the total
of
these numbers are not the correct total. It is even calculating the
decimals
and then rounding off
The problem with formatting a cell is that it is only cosmetic; that is, it
only changes the **display** of a value to match the format, but the
original number remains as typed in behind the scenes. You can see that by
clicking on a cell you entered data in and looking at the formula bar... the
original number, as typed in, will still be displayed there.
I am newly returned to Excel after a lengthy absence from it, so the
following may or may not be the best way for you to proceed (check back here
later on to see if you receive different advice from the newsgroup
regulars). Okay, with that said, let us proceed...
You can use a worksheet Change event macro to physically change the typed in
value by the user. However, there is a question as to how you want that
change to take place. You can truncate off any decimal value as if they were
never typed in by the user in the first place, or you can round off the
typed in value, but then there are two ways to do rounding in a macro, so
you have to make sure you specify the right one. First off, get into the
Visual Basic editor. Click Tools/Macro/VisualBasicEditor on Excel's menu bar
(or simply key in Alt+F11). Once in there, double-click on Sheet you want to
apply this macro to in the Project Explorer window (it is the one with a
tree view of the Books and Worksheets you have in your Excel project)
located in the top, left of the Visual Basic Editor window. If you don't see
it, key in Ctrl+R to force it to appear.
Okay, after you double-click the Sheet you want to apply the macro to in the
Project Explorer window, a Code window will appear in the main area of the
Visual Basic Editor. Copy/Paste the following into the open area of that
Code window...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then Target.Value = Int(Target.Value, "0")
End Sub
The above code will **truncate** away any decimal values typed by the user
IN COLUMN 5; that is, column "E". (If you want to apply this procedure to a
different column, change the 5 in the IF statement in any of the procedures
shown in this posting to the column number that you want to apply it to.)
Truncation means that even if your user types in something like 123.99999,
only 123 will be left in the cell once they either hit the Enter key or move
to another Cell. If that is not what you want, there are two **rounding**
options available to you. Here is the one to duplicate the rounding you see
when you applied the format to your cell(s) originally. Instead of the code
above, Copy/Paste this code into the Code window instead...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then Target.Value = Format(Target.Value, "0")
End Sub
This performs what most people consider "normal" rounding (if there is a
decimal of exactly .5 after the number, then the number is **always**
rounded upward to the next highest whole number. As I said, that is what the
Excel spreadsheet does. The alternative to "normal" rounding is what VBA
uses for its built-in Round function, something known as "Banker's
Rounding". In this method, numbers ending in exactly 5, which are to be
rounded to the previous decimal position, are rounded towards the previous
even number. So, this method will round 13.5 to 14 like normal rounding does
(because the number in front of the exact 5 ending is odd), but it will
round 12.5 to 12 (because the number in front of the exact 5 ending is
even). All other roundings for the Round function are the same as for normal
rounding...the only difference between the two is when the number ends in
exactly 5 and you want to round to the previous decimal position. That last
statement means the rounding effect is not only for halves. If your number
is 123.45 and you want to round to a single decimal place, Banker's Rounding
would round the number to 12.4 whereas "normal" rounding would make it
123.5. Okay, with that explanation out of the way, IF you want to apply this
Banker's Rounding method to your entries, then use the following code
instead of either of the above procedures...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then Target.Value = Round(Target.Value, 0)
End Sub
That's it, once one of the code procedures above is placed in your Code
window, you can exit the Visual Basic Editor and any value entered into
column 5 ("E") will be changed automatically... and that change is physical,
not cosmetic.
Rick