CODEWITHSUNDEEP
excelvbaexcel-formularounding
Nilusha M.
Nilusha M.

Reputation: 57

How to round off the specific range value via excel macro

I need to round off the range("M2:M13") value to two decimal points.I wrote the below code using "NumberFormat" option but when I copy paste this value to another workbook it displays me the whole number in formula bar and the rounded off number in the selected cell.

How do I completely round off the numbers to two decimal points?

Sub RoundOff()
    Worksheets("Sheets2").Activate
    Range("M2").Select
    Selection.NumberFormat="0.00"   
    ActiveCell.FormulaR1C1="RC[-3]/RC[-4]*100"
    Range("M2:M13").Select
    Selection.FillDown
End Sub

Upvotes: 0

Views: 1459

Answers (2)

Oishik Sinha
Oishik Sinha

Reputation: 83

You can do by simply doing range("A1").EntireColumn.NumberFormat = "#.00"

It will format the entire A column

Upvotes: 0

Error 1004
Error 1004

Reputation: 8230

@Nilusha M. It s missing one "=" before "RC[-3]/" from your formula.

You can try:

Sub Test()
    With ThisWorkbook.Worksheets("Sheet2")
        .Range("M2").FormulaR1C1 = "=RC[-3]/RC[-4]*100"
        With .Range("M2:M13")
            .FillDown
            .NumberFormat = "0.00"
        End With
    End With
End Sub

Upvotes: 2

Related Questions