Solved: Use round in VBA?

[Blackie50]

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.

[VBA]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[/VBA]

How do I get the round function in there?

thanks
Jon

[Blackie50]

Think its just a question of putting 'round' in front and bracketing the range code.

Will test

[Bob Phillips]

Why are you saying it is wrong? What you see is the rounded value, so isn't that correct?

[Blackie50]

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]

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]

Nope - didn't know that

thanks
Jon