Rounding off any cell with numbers using VBA

Question

I have a non fixed set of data that may vary in rows and columns.

I intend to use VBA to search the whole dataset and round off all cells with numbers to a specified decimal place. (or rounded to nearest 100s or 10s too)

I have the following code:

Sub roundthissheet()
Dim cell As Range, rng As Range

rng = Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select

For Each cell In rng.cells
    If cell.Value <> "" And IsNumeric(cell.Value) Then
        cell.Value = Round(cell.Value, -2)
    End If
Next cell
End Sub

However, my if statement does not seem to run at all and I encounter the error message "Object variable or With block variable not set"

How shall I proceed with regards to this error?

For the error you mention, note that objects need to be `Set`. So you should have `Set rng = Range("A1",...)` Of course, there are other errors in your code as noted in some of the answers below. — Ron Rosenfeld, Mar 13 '19 at 12:21
Note that VBA's `Round` function uses so called *bankers' rounding* which might return unwanted results; it's a standard form of rounding used in financial and statistical operations in order to minimize significant rounding errors over multiple rounding operations by consistently rounding midpoint values in a single direction - c.f. [How Excel VBA rounds doubles...](https://stackoverflow.com/questions/46850958/how-excel-vba-rounds-doubles-to-integers/48042374#48042374) An alternative would be to use `WorkSheetFunction.Round` instead. — T.M., Mar 13 '19 at 14:08

score 1 · Answer 1 · answered Mar 13 '19 at 09:03

If you just want to set the number format then try this

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range

    Set ws = Sheet1 '<~~ Change this to the relevant sheet

    With ws
        On Error Resume Next
        Set rng = .Cells.SpecialCells(xlCellTypeConstants, 1)
        On Error GoTo 0

        If Not rng Is Nothing Then rng.NumberFormat = "0.00"
    End With
End Sub

If you want to round it then you do not need to loop through all cells. Use SpecialCells to loop through only cells which have number

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range, aCell As Range

    Set ws = Sheet1 '<~~ Change this to the relevant sheet

    With ws
        On Error Resume Next
        Set rng = .Cells.SpecialCells(xlCellTypeConstants, 1)
        On Error GoTo 0

        If Not rng Is Nothing Then
            For Each aCell In rng
                aCell.Value = Round(aCell.Value, 2)
            Next
        End If
    End With
End Sub

score 1 · Answer 2 · answered Mar 13 '19 at 09:04

If you're setting a range, you need the SET command, and you should not .Select while setting:

rng = Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select

becomes:

Set rng = Range("A1", ActiveCell.SpecialCells(xlLastCell))

Also, in your rounding you have specified a negative value.

cell.Value = Round(cell.Value, -2)

Becomes:

cell.Value = Round(cell.Value, 2)

So the code ends up as so:

Sub roundthissheet()
    Dim cell As Range, rng As Range
    Set rng = Range("A1", ActiveCell.SpecialCells(xlLastCell))
    For Each cell In rng.Cells
        If cell.Value <> "" And IsNumeric(cell.Value) Then
            cell.Value = Round(cell.Value, 2)
        End If
    Next cell
End Sub

score 0 · Answer 3 · answered Mar 13 '19 at 08:59

You may this.

Sub RoundValues()
Dim cel As Range, rng As Range

Set rng = Range("A1", Range("A1").End(xlDown).Address)

    For Each cel In rng
        If cel.Value <> "" And IsNumeric(cel.Value) Then
            cel.Value = Round(cel.Value2, 2)
        End If
    Next cel

End Sub

Rounding off any cell with numbers using VBA

3 Answers3