none
Unwanted Rounding When Copying Between Worksheets RRS feed

  • Question

  • Has anyone else seen this, or can anyone explain why this is happening?

    When I want to copy data from a range on one worksheet to a range in another, I've used both of these methods:

            Set rngcopyfrom = wss.Range("B37:E40")
            Set rngcopyto = wst.Range("C53:F56")
            rngcopyto.Value = rngcopyfrom.Value

    And

            wss.Select
            Range("B37:E40").Select
            Selection.Copy
            wst.Select
            Range("C53").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

    I noticed that when I use the first method, the values in the pasted data get rounded to 2 digits of precision. The second method does not round the numbers.  The data I'm copying are numbers only (no formulas), and neither the source or the target have "precision as displayed" turned on.  

    Excel 2003, SP3

    Wednesday, June 29, 2011 5:41 PM

All replies

  • After a bit more research, I found the problem.   When you refer to the Value property of a Range, if any of the cells are formatted as currency, VBA will truncate them to 2 digit precision.   It does a similar thing with Dates. 

    See http://forums.techarena.in/windows-software/1314495.htm  for a better explanation of this.

    I resolved this unwanted behavior in my code by using Range.Value2 instead of Range.Value.  Value2 treats the numbers as doubles.

    Aldo

    Wednesday, June 29, 2011 7:03 PM
  • On Wed, 29 Jun 2011 19:03:34 +0000, aldoman wrote:
     
    >
    >
    >After a bit more research, I found the problem.   When you refer to the Value property of a Range, if any of the cells are formatted as currency, VBA will truncate them to 2 digit precision.
     
    I'm pretty certain that should be 4 decimal digits.  The Currency data type has (up to) 15 digits to the left of the decimal point, and 4 digits to the right.
     

    Ron
    Wednesday, June 29, 2011 7:30 PM