ROUNDDOWN In Cell Format ONLY?

[Centrican]

Hey Guys,

Another bizzarre request from me:

I'm looking for a way to apply a rounddown function to values in cells, but to their format only, not the number itself.

e.g. I'm wanting to be able to enter 1.4 and 1.7, have both display as 1, yet add together to display 3.

I've tried everything I can think of to solve this, I'm starting to wonder if it is even possible.

Anyone got any thoughts about how I can achieve it?

 

[Scott Huish]

Private Sub Worksheet_Calculate()
Dim c As Range
For Each c In ActiveSheet.UsedRange.Cells
    If IsNumeric(c) And c <> "" Then c.NumberFormat = Fix(c) & ";" & Fix(c)
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
For Each c In Target
    If IsNumeric(c) And c <> "" Then c.NumberFormat = Fix(c) & ";" & Fix(c)
Next
Application.EnableEvents = True
End Sub

 

[Centrican]

Hehe yeah the numbers are likely to be a lot higher than 31!

Ok, no worries I will do it with code, I just wanted to avoid having to change about 3000 pieces of output code.

Cheers for the input guys.

 

[Scott Huish]

OK, it's been pointed out that you can't have more than around 200 unique number formats, so it kinda depends on how many different numbers you might have.