macro for rounding values

[RAM72]

Hi All

Have a summary sheet in which I have a column with numerical values of 2 decimal place.

However I am looking for a macro to automate process through a button to whole numbers as per attached file tp last data row

Thanks for help

 

[Marc L]

Hi !

What do you expect for example 163.5 ?
​

 

[RAM72]

Hi
What i expect 163.5 to read 164

so as example if any value 100.01 to 100.49 then round 100

if 100.50 to 100.99 then round 101

see attached in red with round formula in cells

 

[Marc L]

Sub Demo()
    With ActiveSheet
        With .[F2].Resize(.UsedRange.Rows.Count - 1)
             .Formula = "=ROUND(E2,0)"
             .Formula = .Value
        End With
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Hui]

If you wanted to replace the original data instead of add a Column

Dim c As Range

For Each c In Range("E2:E10")
  c.Value = Application.WorksheetFunction.Round(c, 0)
Next

 

[RAM72]

Sub Demo()
    With ActiveSheet
        With .[F2].Resize(.UsedRange.Rows.Count - 1)
             .Formula = "=ROUND(E2,0)"
             .Formula = .Value
        End With
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

Thanks Marc workings as requested

 

[RAM72]

If you wanted to replace the original data instead of add a Column

Dim c As Range

For Each c In Range("E2:E10")
  c.Value = Application.WorksheetFunction.Round(c, 0)
Next

Hi Hui

thank you working but could you adjust code as long as last data row .

 

[Hui]

Dim c As Range

For Each c In Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row)
  c.Value = Application.WorksheetFunction.Round(c, 0)
Next

 

[jindon]

Or

Sub test()
    With Range("e2", Range("e" & Rows.Count).End(xlUp))
        .Value = Evaluate("index(round(" & .Address & ",0),)")
    End With
End Sub

 

[mohadin]

Hi, Mr. hui & Mr. jindon
witch of these code is faster
If I may ask
thank you both

 

[RAM72]

Dim c As Range

For Each c In Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row)
  c.Value = Application.WorksheetFunction.Round(c, 0)
Next

thank you Hui

 

[jindon]

Hi, Mr. hui & Mr. jindon
witch of these code is faster
If I may ask
thank you both

You can test it for yourself...
e.g

Sub test()
    Dim s As Single
    s = Timer
    With Range("e2", Range("e" & Rows.Count).End(xlUp))
        .Value = Evaluate("index(round(" & .Address & ",0),)")
    End With
    MsgBox Format$(Timer - s, "0.00000")
End Sub

With 95,000 rows
Mine: 0.24219
Other: 5.53125

 

[Hui]

If you want to speed it up try:

Dim myArr As Variant
myArr = Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row).Value2
For i = 1 To UBound(myArr, 1)
  myArr(i, 1) = Round(myArr(i, 1))
Next i
Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row) = myArr

123 Records
Huis 0.03125 sec
Jindon 0.00391 sec
Huis 2 0.00391 sec

95000 Records
Huis 8.89063 sec
Jindon 0.14453 sec
Huis 2 0.07422 sec

 

[mohadin]

Hi Hui,
Thanks

Dim myArr As Variant
myArr = Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row).Value2
For i = 1 To UBound(myArr, 1)
  myArr(i, 1) = round(myArr(i, 1), 0)
Next i
Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row) = myArr

End Sub

myArr(i, 1) = Application.WorksheetFunction.round(myArr(i, 1), 0)

Am I correct?

 

[Hui]

@mohadin

Using the worksheet function round gives you more control, but uis so much slower than the VBA Round

So myArr(i, 1) = round(myArr(i, 1) )
is much faster than
myArr(i, 1) = Application.WorksheetFunction.round(myArr(i, 1), 0)

Note: The two Round function have a slightly different syntax

 

[mohadin]

@mohadin

Using the worksheet function round gives you more control, but uis so much slower than the VBA Round

So myArr(i, 1) = round(myArr(i, 1) )
is much faster than
myArr(i, 1) = Application.WorksheetFunction.round(myArr(i, 1), 0)

Note: The two Round function have a slightly different syntax

but It does not like it
myArr(i, 1) = round(myArr(i, 1) )
I'm using office 2013

 

[Hui]

  myArr(i, 1) = Round(myArr(i, 1))

works ok in Excel 2016

I cannot test in 2013

You can replace Round with

myArr(i, 1) = Int(myArr(i, 1) + 0.5)

if you want

 

[RAM72]

Or

Sub test()
    With Range("e2", Range("e" & Rows.Count).End(xlUp))
        .Value = Evaluate("index(round(" & .Address & ",0),)")
    End With
End Sub

thank you a lot Jindon

 

[mohadin]

  myArr(i, 1) = Round(myArr(i, 1))

works ok in Excel 2016

I cannot test in 2013

You can replace Round with

myArr(i, 1) = Int(myArr(i, 1) + 0.5)

if you want

well,thank you very much in dead
and I'll try to get 2016 on my pc
appreciate