Pasted values have 13 decimal places (instead of 1)

stv

New Member
Joined
Jan 27, 2009
Messages
6
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Rich (BB code):
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.
 

stv

New Member
Joined
Jan 27, 2009
Messages
6
Thanks JB and Mike for your suggestions .... they both work and have resolved this problem.:)

regards
steve
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,427
Messages
5,837,173
Members
430,481
Latest member
nizambakhshi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top