VBA Round function and pasting result into a cell

[Skiff]

Hi, I've got this problem that I don't uderstand.

I'm calculating values in VBA, and then pasting them into a cell

PLRBG = Round(czLak * (1 - rabat) + 0.00001, 2)
Range("T13").Value = Round(PLRBG, 2)

And I completely don't understand why cell Value is 1,51999998092651 instead of 1,52 ???
Do I really have to make calculations on sheet with Excel functions and then paste just results to cells?
Or is there a way to force two places decimal precision into cell from VBA?

Regards,
Skiff

 

[dchaney]

Just add a numberformat to your code

PLRBG = Round(czLak * (1 - rabat) + 0.00001, 2)

With Range("T13")
    .Value = Round(PLRBG, 2)
    .NumberFormat = "0.00"
End With

 

[Skiff]

Thanks, but doesn't this only affects display?
I've got format set, but in my code all calculations (many of them) are done in VBA, and now I'm concerned about proper calculations.

On every value calculation needs to be exactly two decimal points.
Any other may seriously impact data values (one exception is multiplication by 1.115).

That is just small part of calculations done:

   CzesciNetto = Round(czesci * (1 - rabat) + 0.00001, 2)
   'CzesciBrutto = Round(czesci * 1.115 * (1 - rabat) + 0.00001, 2)
   CzesciBrutto = Round(czesci * 1.23 * (1 - rabat) + 0.00001, 2)
   
   LakierNetto = Round(material * (1 - rabat) + 0.00001, 2)
   'LakierBrutto = Round(material * 1.115 * (1 - rabat) + 0.00001, 2)
   LakierBrutto = Round(material * 1.23 * (1 - rabat) + 0.00001, 2)
   
   NormNetto = Round(normalia * (1 - rabat) + 0.00001, 2)
   'NormBrutto = Round(normalia * 1.115 * (1 - rabat) + 0.00001, 2)
   NormBrutto = Round(normalia * 1.23 * (1 - rabat) + 0.00001, 2)

   PbmRBG = Round((czBlach + czMech + czDod) * (1 - rabat) + 0.00001, 2)
   PbmSt = stBlach
   PbmWartNetto = Round(PbmRBG * PbmSt + 0.00001, 2)
   'PbmWartBrutto = Round(PbmRBG * 1.115 * PbmSt + 0.00001, 2)
   PbmWartBrutto = Round(PbmRBG * 1.23 * PbmSt + 0.00001, 2)

   PLRBG = Round(czLak * (1 - rabat) + 0.00001, 2)
   PLSt = stLak
   PLWartNetto = Round(PLRBG * PLSt + 0.00001, 2)
   'PLWartBrutto = Round(PLRBG * 1.115 * PLSt + 0.00001, 2)
   PLWartBrutto = Round(PLRBG * 1.23 * PLSt + 0.00001, 2)

 

[dchaney]

Ahhh got ya... You are using the Round function correctly, so not sure why it is going beyond the 2 decimal unless it is the +0.0001 your using...

when I use the code you have and use the debug.print function I am only getting 2 decimal places... Have you tried the debug.print to make sure it is not just a formatting issue in excel?

 

[Skiff]

That is an issue. On code break I always get two decimal places, only in cell there are more.
+0.0001 shouldn't be the issue since I don't have this on cell value paste:

Range("T13").Value = Round(PLRBG, 2)

Any ideas?

 

[dchaney]

I have no idea why it is doing this to you... every number I add to the code you have above comes out with 2 decimals, if I change the formatting on the sheet it just adds 0s behind the 2 decimals... I even get 2 decimal when multipling by 1.115 with round(...,2)

WHat values are getting you the long decimal string? I want to try to recreate this so I can help you

 

[Skiff]

Thanks for help. But at this point I can attach all VBA code or even whole Excel file.
On code break every number I get is shown in two decimal places, so I cannot think other way of checking it

Are you really willing to help me this much? Thanks in advance.

 

[dchaney]

Are you really willing to help me this much? Thanks in advance.

Sure am

Post your VBA let me look it over (I can not get to files here at work) maybe I can help you out...

 

[yky]

[COLOR=#333333]PLRBG = Round(czLak * (1 - rabat) + 0.00001, 2)
[/COLOR][COLOR=#333333]Range("T13").Value = Round(PLRBG, 2)[/COLOR]

Two questions:

1. What is the number 0.00001 doing in the formula? Surely it'll be rounded off (if rounding works as it should be). Isn't ROUND(anything + 0.00001,2) = ROUND(anything,2)?

2. Why do you have this two-stage rounding? Can you just use:

[COLOR=#333333]Range("T13").Value[/COLOR][COLOR=#333333]= Round(czLak * (1 - rabat) + 0.00001, 2)[/COLOR]

 

[Skiff]

Hi yky.
1. I found on internet that VBA function Round() 1.5 rounds down, so there was advice to add small amout to ensure round up (cannot find this page right now
2. Two stage rounding was included only when I noticed that cells value are longer that two decimal places. It was not there before. Either way value put into a cell is not rounded up

@dchaney: I will check numbers without +0.0001 and then attach my file, but it will be tommorow. Too much tasks at work right now. Big thanks for help