Results 1 to 9 of 9

20041213, 20:02 #1
 Join Date
 Jun 2001
 Posts
 56
 Thanks
 0
 Thanked 0 Times in 0 Posts
Custom Numeric Format (Excel 2003/2002)
I'm stumped. I've written a macro to apply a numeric format based on a cell's value. In the attached file, the value entered in cell D3 is used to calculate the range D619 & F6:F19. Formatting should be applied as follows:
Values less than 1 & values with decimal places  0.0#
Whole positive numbers  0, no decimal places.
The macro is working fine except in cell F19 when some multiples of 12 are entered in D3. For example when D3 is 12 or 24, F19 shows a decimal place, which it shouldn't. However, when D3 is 36 no decimal place appears, which is correct. All 3 values return a whole positive # with no decimal places. It doesn't appear to be a rounding error & I believe my macro logic to be correct, but I can't see where the problem is. Any help would be much appreciated. thanks,
P.S. the code is :
Range("f17:f19").Select
Dim MyCell
For Each MyCell In Selection
If MyCell.Value < 1 Then ' #'s less than 1
MyCell.NumberFormat = "0.0#"
ElseIf MyCell.Value / Int(MyCell) = 1 Then 'whole #'s, no decimals
MyCell.NumberFormat = "0"
Else
MyCell.NumberFormat = "0.0#" ' whole #'s with decimals
End If
Next

20041213, 20:20 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Custom Numeric Format (Excel 2003/2002)
It's due to tiny rounding errors. Excel stores numbers in binary format, while they are displayed as decimal numbers. The conversion between decimal and binary is exact, resulting in very small rounding errors. Normally, Excel suppresses these, but in this situation, they affect the calculations. The result of MyCell.Value / Int(MyCell) is not excactly 1 for cell F19, there is a difference of 2.22044604925031*10^16 or about 0.0000000000000002. If you replace the line
ElseIf MyCell.Value / Int(MyCell) = 1
by
ElseIf MyCell.Value Mod 1 = 0
you should be safe.

20041213, 20:27 #3
 Join Date
 Jun 2001
 Posts
 56
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Custom Numeric Format (Excel 2003/2002)
Hans,
Thanks so much. That seems to have done it. I suspected it may have been some rounding, but when I displayed all 15 decimal places they were all 0. What does this line actually saying.

20041213, 20:30 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Custom Numeric Format (Excel 2003/2002)
The Mod function computes the remainder of the first number after dividing by the second number. MyCell.Value Mod 1 is the remainder of the cell value after dividing by 1, i.e. the fractional part. If this is 0, the cell value was a whole number.

20041214, 14:07 #5
 Join Date
 Jun 2001
 Posts
 56
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Custom Numeric Format (Excel 2003/2002)
Hans,
thanks! I really needed that.

20041217, 15:13 #6
 Join Date
 Jun 2001
 Posts
 56
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Custom Numeric Format (Excel 2003/2002)
I thought I had it licked, but......I understand the logic, but in practice it just isn't working! As recommended I changed the line to read: MyCell.Value Mod 1=0. But when I run the macro in Step mode with a watch, a cell containing the value 2.4 MyCell.value mod 1 evaluates to 0, when it should in fact be .4. As a result it's not applying the correct numeric format. Even when I change the line to read MyCell.Value mod 1 >0 It's still not applying the correct format. I don't know what to do here. I'm hoping there's just some little thing I've missed. Any suggestions??? Thanks.

20041217, 15:26 #7
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Custom Numeric Format (Excel 2003/2002)
The MOD function returns an interger result, therefore it will not do what you are trying to do. Try this:
<pre>Dim dFracPart as Double
dFracPart = MyCell.Value  Int(MyCell.Value)
</pre>
Legare Coleman

20041217, 15:30 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Custom Numeric Format (Excel 2003/2002)
Hmm, apparently MOD acts differently from what I expected. Try this:
<code>ElseIf Abs(MyCell.Value  Round(MyCell.Value)) < 0.000000001 Then</code>
This version compares the cell value to the value rounded to a whole number, and if the difference is very small, assumes that the number itself was a whole number. The 0.000000001 is to take small rounding errors into account.

20041220, 16:30 #9
 Join Date
 Jun 2001
 Posts
 56
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Custom Numeric Format (Excel 2003/2002)
That one did it. Thanks!!!!!!!!!!!