MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA Rounding


Posted by Doug on October 18, 2000 7:33 AM

Can someone help me to a rounding procedure in VBA? Is it something like?:

Set myRange = Worksheets("DashConverted").Range("F1:F1000")
Application.WorksheetFunction.Round (myRange, 4)

Thanks,


Posted by Jim Knicely on October 18, 2000 9:50 AM

Off the top of my head, you can do this in a procedure:
just an example ...

Sub RoundIt()

Dim MyRange As Range

Application.ScreenUpdating = False
Set MyRange = Range("A1:A3")

For Each Cell In MyRange
Cell.Value = Round(Cell.Value, 2)

Next Cell

End Sub

Posted by Jim Knicely on October 18, 2000 9:52 AM

Application.ScreenUpdating = False Set MyRange = Range("A1:A3") For Each Cell In MyRange Cell.Value = Round(Cell.Value, 2)

Of cource, using your exaple ranges, it would be: For Each Cell In MyRange Cell.Value = Round(Cell.Value, 4)

Posted by Doug on October 18, 2000 10:35 AM

Excel didn't recognize the Round command all by itself, so I tried the below and got "type mismatch". No dice......

Set MyRange = Range("F1:F1000")
For Each Cell In MyRange
Cell.Value = Application.WorksheetFunction.Round(Cell.Value, 4)
Next Cell

Posted by Doug on October 18, 2000 10:48 AM

What I did above works fine. My problem was that there was a label in the defined range. That gave me "Type Mismatch error". When I left the label cell out of the range, it worked fine. Another question, if the cell is blank, it puts in 0.0000. Any way to skip blank cells?

Set MyRange = Range("F2:F1000") --> f1 had a label
For Each Cell In MyRange
Cell.Value = Application.WorksheetFunction.Round(Cell.Value, 4)
Next Cell
End Sub

Posted by Doug on October 18, 2000 11:00 AM

I love answering my own questions.........

pretty crappy goto loop. Anyone have a cleaner solution for skipping over blank cells?

Set MyRange = Range("F2:F1000")
For Each Cell In MyRange
If Cell.Value = "" Then
GoTo 10
End If
Cell.Value = Application.WorksheetFunction.Round(Cell.Value, 4)
10: Next Cell
End Sub

Posted by Tim Francis-Wright on October 18, 2000 1:50 PM

Re: I love answering my own questions.........

how's this?

Set MyRange = Range("F2:F1000")
For Each Cell In MyRange
If Cell.Value <> "" Then
Cell.Value = Application.WorksheetFunction.Round(Cell.Value, 4)
Next Cell
End Sub