Pasted values have 13 decimal places (instead of 1)

[stv]

Hi

I have a some code (which I did not write) that copies some data (for a particular date) from one worksheet and pastes it into cells related to the same date on a different worksheet.

Everything works OK, except that when the data contains a decimal place (e.g. 173.3) the pasted value on the other sheet has 13 decimal places instead of 1 (e.g. 173.300003051757 instead of 173.3.)

Other examples are:
177.2 177.187006103515
173.1 173.100006103515
177 177

The variables that contain the values are defined as "Single", and I have also checked the variable values (via a watch) prior to the pasting process and the values seem to be correct (i.e. they do not contain the extra decimal places.)

However when I check the actual "target" cells on the target worksheet, the values contain the extra decimal places (e.g.173.300003051757.)

I am using Excel 2003, with Windows XP.

The format of the target cells is "general" .

So what I am looking for is simple way of removing the extra decimal places by the use of a macro.

I was thinking of a macro that would compare the source and target cells (by doing a lookup on the common date) and then replacing the target cell with the source cell if the target cell has been corrupted (by now having the extra decimal places.)

I would also need to run the macro each day after the copying process has been run.

Would anyone have any ideas on this?

thanks

regards
steve

 

[jbeaucaire]

Sub DecFix()
Dim rng As Range, lastrow As Long
lastrow = Range("K" & Rows.Count).End(xlUp).Row
Set rng = Range("K1:K" & lastrow)
    
    For Each cell In rng
        cell.Value = Format(cell.Value, "#.0")
    Next cell

End Sub

Adjust that code above to the column you actually want to do this work in.

 

[mikerickson]

Have you tried replacing Single with Double in the VB code?

 

[stv]

Thanks JB and Mike for your suggestions .... they both work and have resolved this problem.

regards
steve