VBA: .Formula = "=" Cells.Value with a lot fo decimals

[CedricMattelaer]

Hello all,

This is a follow-up on a previous question (http://www.mrexcel.com/forum/showthread.php?p=2935323#post2935323), as I just now found out what seems to be causing the problem, I prefer to post it in a specific thread.

I have a formula which simplified (without loops and variables) goes as follows:

.Cells(592, 22).Formula = "= " & .Cells(592, 22).Value & " + " & .Cells(676, 18).Value

I want to add a specific value to the value already in that cell, and prefer to keep them written as a sum.

Now, sometimes the evaluation of this code results in an error. After long debugging, I found out that this happens in cells for which .Cells(592, 22) contains a lot of decimals (for example 0,999999999996).
If I change this number to 1, the program does what it should do without any problem.

I tried to set the decimal designator to . instead of a comma (",") but that doesn't make any difference.

Of course, I can first replace each cell by its rounded equivalent, but that's not very effective...

Is it normal that this happens? What is it due to? Is there any way around it?

If anyone has any clues, thanks for sharing!

 

[wigi]

Here's a simple idea, but often simple ideas work out quite well.

.Cells(592, 22).Formula = "=" & INT(.Cells(592, 22).Value) & " + " & .Cells(676, 18).Value

 

[CedricMattelaer]

Thanks! That seems to be working.
I still don't know why it failed, but converting the cellvalue to integer did the trick.

 

[wigi]

If you want to read on that topic, just Google on

excel precision
or
floating point numbers

many articles are devoted to that topic, including very good ones.

 

[CedricMattelaer]

Thanks for the tip