Re: Decimal

Tech-Archive recommends: Speed Up your PC by fixing your registry

You can use a work*** Change event macro to physically change
the typed in value by the user. . . .
...

Generally unwise. Better to leave entries as-is and transform the
entered values as needed in formulas.

I wholeheartedly agree... and I had planned to include a mention of that, but see I forgot to include it (I guess I got carried away with all that other stuff I wrote).


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". . . .
...

Copy & paste is a killer! VBA's Int(..) function takes one and ONLY
one argument. The Int call above is a syntax error.

Yes, you are right. I should have used code similar to this instead...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
For Each R In Target
If R.Column = 5 Then R.Value = Int(R.Value)
Next
End Sub


But code isn't necessary. To sum a column of numbers rounded to
integers, use

=SUMPRODUCT(ROUND(range,0))

To sum a column of numbers truncated to integers, use

=SUMPRODUCT(TRUNC(range))

and to sum using bankers rounding, use

=SUMPRODUCT(ROUND(range-(MOD(range*2,4)=1)/2,0))

Excellent... a far, far, far better approach than the one I posted! Thanks for following up with that.


Rick

.

Quantcast