Value Rounded when copied from another cell

M

mandalorian2

I have a function which just sets the values several cells in one workbook
equal to the values of the cells in another workbook.

When I do this some cells get rounded oddly. For example:

the source cell formatted as a 3 decimal place currency cell and is rounded
to three decimal places using this equation
"=ROUND('Raw Data'!V7/(1-C4),3)" ('Raw Data'!V7 is pulled from a SQL DB
and is equal to 0.372512437810945) C4=15%, so the source cell should equal
exactly 0.438 which is what is displayed.

The recieving cell which happens to be in another workbook is formatted as a
3 decimal place currency cell.

I use the following line of code to set the values:
Workbooks(cCurrent_Quote_ID).Worksheets(4).Range("D19").Value =
Workbooks(ThisWorkbook.Name).Sheets("Cat Over \$15K Form").Range("D19").Value

This works fine except that the value in the recieving cell is set to 0.440
this is a price per unit so in large quantities this can make a serious
difference.

Does anyone have any idea what could be causing this?

P

Paul C

VB rounds to 2 decimal places unless you explicitly tell it not to.

Try this

Workbooks(cCurrent_Quote_ID).Worksheets(4).Range("D19").Value =
FormatNumber(Workbooks(ThisWorkbook.Name).Sheets("Cat Over \$15K
Form").Range("D19").Value,3)

M

mandalorian2

Thanks that did the trick.

Seems an odd thing for the designers to have done considering its a program
designed to perform accurate mathmatical calculations though.

D

Dave Peterson

You may want to use .value2. It makes a difference if the cell is formatted as
a date or currency:

Workbooks(cCurrent_Quote_ID).Worksheets(4).Range("D19").Value = _
Workbooks(ThisWorkbook.Name).Sheets("Cat Over \$15K Form").Range("D19").Value2