0

I have a cell containing a number with 6 decimal places and I wish to assign it to another worksheet's cell with 5 decimal place precision. I am using the same variable twice and it works correctly the first time I make an assignment but during the second assignment statement of the Cells Value to a double variable the value is rounded to four decimal places automatically. I do not have Excel Option to use displayed precisoin turned on. 

Dim i As Long, x As Long, round_prc As Double
Dim WSTarget As Worksheet

        'OE Price
        round_prc = Round(.Cells(3 + x, 9).Value, 5)    ; six decimals assigned to 5 decimals
        WSTarget.Cells(1 + i, 10).Value = round_prc

       'New Price
        round_prc = .Cells(3 + x, 13).Value     ; six decimals assigned to 4 decimals??????
        round_prc = Round(.Cells(3 + x, 13).Value, 5)
        WSTarget.Cells(1 + i, 12).Value = round_prc

UPDATE: The Cell Format differs from the OE Price and the New Price. New Price includes a $. When I remove the $ the assignment no longer rounds. Why would currency automatically round to 4 decimal places? Can anyone explain that?

Community
  • 1
  • 1
Captain Bob
  • 21
  • 3
  • Under `'New Price` why are you assigning round_prc twice? Why not just assign it once in the same way you assigned it under `'OE Price`? It could be that the first assignment is truncating the variable to 4 decimal places for some reason, and then there aren't 5 decimal places left to round to the second time you assign the variable. – tittaenälg Oct 02 '15 at 17:50
  • I added the first assindment to check the value during a debug session. Both assignments are rounding the value in round_prc to 4 decimal places. Thanks. – Captain Bob Oct 02 '15 at 18:00
  • Since you added the information about one being currency formatted, it sounds like you should use `Value2` instead of `Value`. I was just looking up some information about that, and this may be helpful: http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 – tittaenälg Oct 02 '15 at 18:42

1 Answers1

2

I resolved my issue. The fact that the cell was formated as currency, forced the .Value to be rounded to 4 decimal places. There is no such constraint on the .Value2 property. Thus....

       'OE Price              a general formatted cell
        round_prc = Round(.Cells(3 + x, 9).Value, 5)   'rounded only by the function
        WSTarget.Cells(1 + i, 10).Value = round_prc


        'New CCA Price         a currency formatted cell
        round_prc = .Cells(3 + x, 13).Value            'rounded to 4 decimals
        round_prc = .Cells(3 + x, 13).Value2           'Not rounded
        round_prc = Round(.Cells(3 + x, 13).Value2, 5)
        WSTarget.Cells(1 + i, 12).Value = round_prc

I hope this helps others that may encounter this issue.

Captain Bob
  • 21
  • 3