Range.Value = Range.Value, isn't it?

There are several ways to copy information from one sheet to another. Today I discovered an inconsistancy while using something similar like the following code:

Sheet1.Range("A1:A100").Value = Sheet2.Range("A1:A100").Value

The above code does not work correctly if the cells in sheet 2 are formatted in currency- or accounting format. In that case Excel does not copy the original values, but instead the values rounded as they are formatted. In my case amounts such as 12,213045 were copied as 12,21 which led to incorrect results. I figured out what was the problem pretty fast but I'm still wondering why Excel does this. It does not round the numbers if they are formatted as decimals.

Anyway I changed the code into the following which copied the numbers correctly including all its decimals:
Sheet1.Range("A1:A100").Value = Sheet2.Range("A1:A100").FormulaLocal
(see comment below)

For your information, I choose the above code for speed in stead of something like the following code:
Sheet2.Range("A1:A100").Copy
Sheet1.Range("A1:A100").PasteSpecial xlPasteValues
Application.CutCopyMode = False

Update September 2009

Instead of using .Value you can use .Value2 so the code becomes:

Sheet1.Range("A1:A100").Value2 = Sheet2.Range("A1:A100").Value2

That works correct. More information about the .value2 property is on the MS website.

One Response to “Range.Value = Range.Value, isn't it?”

  1. I just found out that in Excel 2003 you shouldn't use the .value = .formulalocal. It is not always reliable. I wrote and tested the code above in Excel 97 and XP and that worked great. However in 2003 Excel somehow the values go wrong. Although I use the formulalocal, the decimal is seen as thousant separator. Even though I use the english version of Excel and have the same regionals settings in Windows.

    After all I suggest you use the copy and paste method.

Pinterest