Rounding error when copying value from one sheet to another

**xned** · 02-28-2010, 01:13 AM

Hello: I am not a VBA programmer but I have gathered enough info to accomplish most of what I set out to, Much thanks to this forum. I am still having an issue with one item. When I try to copy a value from one worksheet to another it is rounding off the number to 2 decimal places rather than 4. eg shows up on sheet 3 as $13.4100 rather than $13.4135.
Both cells are formatted to display currency to 4 decimal places.
The value being copied from sheet one is calculated using a formula
=IF(F31>0,M31/L31,IF(AND(G31>0,E31>0),K30,IF(AND(G31>0,E31=0),M31/L31,"")))

and the code used to transfer the info is:

Please Login or Register  to view this content.

Sheets("Sheet3").Cells(nextrow, "E").Value = Range(Cells(i, "K"), Cells(i, "K")).Value 'ACB/Share

This is the line giving me issues.

On sheet 1 the value is displayed to 4 decimal points but on sheet 3 there are 4 decimal places but the last two are both zero's
Any Ideas on why this is happening

**xned** · 02-28-2010, 01:31 AM

I have included a copy of the spreadsheet in case it helps. There is a button at the top of sheet one called "REPORTS" and the issue is with the tax report button on the form. Specifically the ACB / Share, column E on worksheet 3.

**teylyn** · 02-28-2010, 01:50 AM

Holy cow! A Zip file with almost one MB?!

Could you possibly boil it down to something more manageable? I shudder at the amount of data I'll have to wade through to get to the core of the issue. I'm sure you will be able to re-create the behaviour in a workbook of max 50Kb.

I don't have an unlimited data plan, and I suspect most here don't either.

**xned** · 02-28-2010, 02:00 AM

Will try to strip it down. This has quite a few macro's and formulas in it is why it is so large.

**teylyn** · 02-28-2010, 03:07 AM

Macros and formulas won't blow a file up to a size that its Zip version is 1 MB. Strip down the data. A couple of dozen lines should be sufficient!

**xned** · 02-28-2010, 03:29 AM

Here is the slimmed down version. Only 7 lines of data.
As I mentioned above, The trouble I am having is with the "Reports" Button which opens a userform.
This userform contains 2 buttons for preparing different reports.
I am having trouble with both reports rounding off one of the numbers being copied from worksheet 1 to worksheet 3.
If you type in 2005 and select either the "annual" or "Tax Report" buttons, the ACB / Share value is rounded off to 2 decimal places.