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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

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