Search code examples
excelvbawith-statement

VBA - How to Round values in Range with `With` statement

Using With statement, how do I update .values in Range of cells, to be the result of a Round function, which will take the individual .values as a parameter.

In other words, how to write this:

Range("A1").Value = Round(Range("A1").Value, 0)
Range("A2").Value = Round(Range("A2").Value, 0)

in this form:

With Range("A1:A2")
    .Value = Round(.Value, 0)
End With

I've seen the solution somewhere, but couldn't find it.

Solution

  • Round, or equivalently VBA.Round, does not accept an array as its first parameter.

    Neither does WorksheetFunction.Round.

    Use Application.Round, which is the late-bound form of WorksheetFunction.Round.

    With Range("A1:A2")
    .Value = Application.Round(.Value, 0)
End With

    IMPORTANT NOTE: VBA.Round and WorksheetFunction.Round do not perform the same operation. VBA.Round uses banker's rounding, as noted in its docs. If you need banker's rounding, then you need to loop, but it'd be easier to do so with an array.