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?
 
Ad

Advertisements

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.
 
Ad

Advertisements

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top