PDA

View Full Version : Solved: Use round in VBA?



Blackie50
10-08-2010, 03:08 AM
Hi

Have the following code that splits a number up but its sometomes out because of the roundings e.g. £6200.02 splits to £6200.03.

Range("d18,k18").Value = Range("n7").Value * 10 / 100
Range("d19,k19").Value = Range("n7").Value * 28 / 100
Range("d20,k20").Value = Range("n7").Value * 5 / 100
Range("d21,k21").Value = Range("n7").Value * 5 / 100
Range("d22,k22").Value = Range("n7").Value * 26 / 100
Range("d23,k23").Value = Range("n7").Value * 26 / 100

How do I get the round function in there?

thanks
Jon

Blackie50
10-08-2010, 03:17 AM
Think its just a question of putting 'round' in front and bracketing the range code.

Will test

Bob Phillips
10-08-2010, 03:31 AM
Why are you saying it is wrong? What you see is the rounded value, so isn't that correct?

Blackie50
10-08-2010, 06:03 AM
When I split the number in to each part its then multiplied by a price to
give a number of units. The number (well its actually money) needs to be to 2 decimal places. The split numbers are showing as 2 DP places but actually in the cells they are 3 & 4 decimal places.(which messes the final calc up)

By putting round((target range code),2) it seems to do the trick - didn't think it was the same as it is in an ordinary formula

Just formatting the cells to 2 DP doesn't have any effect.

Bob Phillips
10-08-2010, 06:13 AM
It isn't quite the same, VBA uses Banker's rounding. Banker's rounding rounds to the nearest even number, so that both 1.5 and 2.5 round to 2, and 3.5 and 4.5 both round to 4, whereas Excel always rounds .5 up..

Blackie50
10-08-2010, 06:23 AM
Nope - didn't know that

thanks
Jon