Maintain last 0 if value is ".5", without using simple Round.

[lockarde]

Good morning all,

I have some variables dimmed as doubles, and I'm wondering the easiest way to check if the value ends in .5, for it to show as .50. I can't simply round because I need it show the values out to 3, or 4 decimals if the value is say, 0.375. Is there an easy check for this? The values are typically in increments of .125, if that helps? Any help is greatly appreciated!

lockarde

 

[pgc01]

Hi lockarde

Not sure I understand completely.

Is it not enough to format the numbers to be always displayed with at least 2 decimals? (with for ex. 0.00##)

 

[lockarde]

Hi lockarde

Not sure I understand completely.

Is it not enough to format the numbers to be always displayed with at least 2 decimals? (with for ex. 0.00##)

So um. This is one of those "Doh!" moments. I appreciate the response @pgc01!

 

[pgc01]

I'm glad it helped.

 

[lockarde]

@pgc01, so I'm still having some difficulty with this one actually. I have it written as Format(variable, "#,##0.00") but it still only shows it as ".5" instead of ".50". As far as I can tell my syntax is correct, so I'm not sure what the issue is. When the value is in the thousandths, it displays the number just fine. Am I using the wrong function?

 

[lockarde]

Well, it might not be the most efficient way, but I reverted the initial code as it was (just putting the variable in the cell), and then I used .Range.NumberFormat = "#,##0.000" and that worked. Of course, now it shows an extra zero on any value in the hundredths (.75 shows .750). Certainly not the end of the world - but if you have a better way, I'm all ears!

 

[lockarde]

Ok now I've got it dialed in. I wrote a loop to check if the value = Int(value) +.5, format it with zeros, otherwise leave it alone. Now it looks perfect

 

[Rick Rothstein]

@pgc01, so I'm still having some difficulty with this one actually. I have it written as Format(variable, "#,##0.00") but it still only shows it as ".5" instead of ".50".

The Format function is a VBA "thing" and has no effect on what Excel cells will do with the numbers it formats. In essence, doing this...

YourNumber = 0.5
.Range.Value = Format(YourNumber, "#,##0.00")

does the same thing as if you typed 0.50 into a cell formatted as General... the trailing 0 disappears. You need to use NumberFormat on the range itself to duplicate what Excel's cell formatting does. You sort of anticipated it in Message #8 but didn't carry through correctly. Executing this line of code should correctly format your data...

.Range.NumberFormat = "#,##0.00"

 

[lockarde]

The Format function is a VBA "thing" and has no effect on what Excel cells will do with the numbers it formats. In essence, doing this...

YourNumber = 0.5
.Range.Value = Format(YourNumber, "#,##0.00")

does the same thing as if you typed 0.50 into a cell formatted as General... the trailing 0 disappears. You need to use NumberFormat on the range itself to duplicate what Excel's cell formatting does. You sort of anticipated it in Message #8 but didn't carry through correctly. Executing this line of code should correctly format your data...

.Range.NumberFormat = "#,##0.00"

Thanks for your reply Rick! As you said, it was an implementation error. Once I worked through what excel was doing, I was able to get it dialled in as I wanted. Because I wanted to look at specific values (I have it check for whole numbers, and numbers that end in ".50", I couldn't just apply an end all format - I had to run a check. I have a loop that looks at the values, and formats accordingly. Thanks for all your help guys!