Round a range of cells using VBA

[PureFire]

Im stuck on how to round a range of cells using VBA. At the moment Im having to insert a new column, add the formula in the new column, pastespecial values to the original column then delete the new column. Which is VERY messy.

Ive tried using Application.Worksheetfunction.Round but that only allows 1 cell at a time. Is there a way to do this in VBA ?

 

[panyagak]

For self

Sub MyWork()
For Each cell In [A:A]
If cell = "" Then Exit Sub
cell.Value = WorksheetFunction.Round(cell.Value, 2)
Next cell
End Sub

 

[panyagak]

The above code works well for for column A values only.

I need a code that will apply rounding (to 2 d.p.) to any range I select.

Mine is a large excel sheet with many columns (up to 24) & 1200 rows of unrounded values eg.
5.53356659 to 5.53
2.78976605 to 2.79

Kindly help

Patrick

 

[SpillerBD]

Sub RoundSelection()
Dim CV As Range
'
For Each CV In Selection
On Error Resume Next
If CV.Value <> "" Then
CV.Value = Round(CV, 2)
End If
Next CV
End Sub

 

[SpillerBD]

Actually, did you want Bankers Rounding or Common Rounding?
The VBA function ROUND uses Banker's Rounding.
The Worksheet Function uses common rounding.

 

[panyagak]

Hi SpillerBD

The SAFEST, error-free - my data is sensitive!!

Thanks

 

[SpillerBD]

Banker's Rounding is statistically better. See http://wiki.c2.com/?BankersRounding
However most people are used to seeing the 'other' method.
Because most of your values have values beyond the 3rd decimal place, I don't believe it will matter which you use.

 

[Rick Rothstein]

Actually, did you want Bankers Rounding or Common Rounding?
The VBA function ROUND uses Banker's Rounding.
The Worksheet Function uses common rounding.

All rounding in VB is Banker's Rounding with one exception... the Format function... it does "normal" rounding. So, for two decimal places, instead of this...

Round(Value, 2)

you could use this...

Format(Value, "#.##")

Of course, you could use 0 in place of the # sign to force leading/trailing zeroes as required.

 

[SpillerBD]

All rounding in VB is Banker's Rounding with one exception...

Rick, Do you mean I should expect the same results using the following in the VBE:

[Cells(3, 3).Value = Round(Cells(3, 2).Value, 0)
Cells(3, 4).Value = WorksheetFunction.Round(Cells(3, 2).Value, 0)/CODE]

Test values 0.5 ; 1.5 ; 2.5

 

[Rick Rothstein]

Rick, Do you mean I should expect the same results using the following in the VBE:

[Cells(3, 3).Value = [B][COLOR="#FF0000"]Round[/COLOR][/B](Cells(3, 2).Value, 0)
Cells(3, 4).Value = WorksheetFunction.Round(Cells(3, 2).Value, 0)/CODE]
[/QUOTE]
No, the Round function uses Banker's Rounding... it is the Format function which uses "normal" rounding. Replace the line with the highlighted Round function with this one and then both lines of code will report the same rounded values...

Cells(3, 3).Value = Format(Cells(3, 2).Value, "0")