Error with int() calculation

[DilemmaDave]

Hi All,

This is driving me batty! The int() function returns obviously wrong results and I can't figure out why.

I've simplified things with the following bit of code:

Sub Int_Error()
x = 39.8
y = x * 100
z = Int
End Sub

If you step through this and watch the values , 'z' evaluates as 3979, instead of the correct value of 3980.

I've tested this on two different systems and have the same issue. I'm using Excel 2003, SP3.

z=Int(3980) returns the correct value, so it seems it only happens when the argument is passed as a variable.

This happens consistently with some specific values, 39.8 being one of those values.

Thoughts? Ideas?

Cheers,
David W.

 

[Msadiqrajani]

Try Cint() instead on int

 

[Jon von der Heyden]

Declare your variables, such that x is double, and y and Z are long. Z in fact then becomes redundant.

 

[DilemmaDave]

That does work, but it only works on values to 32,768.

 

[Msadiqrajani]

Then you could try Clng() or declate Z variable as Long

 

[Jon von der Heyden]

Forget Z, use y, but declare y as Long, as I suggest in post #3.

 

[DilemmaDave]

Declare your variables, such that x is double, and y and Z are long. Z in fact then becomes redundant.

The actual expression I am calculating is:

HoleTo = Int(Cells(c.Row, 3) * 100)

When Cells(c.Row, 3) * 100 = 3980, int() returns 3979

Declaring HoleTo as Long does not resolve it.

Thanks

 

[Msadiqrajani]

Dont knows if z is necessary to you..
If not then Jon's tip is simple.

Declare x as double and y as long

It will automatically produce correct answer.

 

[Msadiqrajani]

use this one

dim HoleTo  as long 
HoleTo = Cells(c.Row, 3) * 100

 

[Jon von der Heyden]

You should always qualify the property you wish to use. In this case it is using the default range Value property, but you ought to be using Value2.

 Cells(c.Row, 3).Value2 * 100