MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Rounding using VBA

Posted by Jerid on June 18, 2001 6:07 AM

I'm having trouble rounding a number to two decimal places using VBA.

Here is the code I'm using:

Private Sub AddDailyAccrual()
Dim iX As Integer

Selection.NumberFormat = "#,##0.00"

For iX = 1 To 100
ActiveCell.Value = ActiveCell.Offset(0, 10).Value / ActiveCell.Offset(0, 8).Value
ActiveCell.Offset(1, 0).Select
Next iX
End Sub

Format Column L to Currency, 2 decimal places. Start in cell L1. and move down to L100. Take the value in the cell 10 columns over and divide it by the cell 8 columns over and place it in the active cell.

The code works fine, my problem is that the results of the division doesn't round to 2 decimal places. For example cell V1 = 13319.83 and cell T1 = 29 and the value that you see in cell L1 is 33069.92, BUT if I click on the cell the real value is 33069.9227586207, and I need for it to be only 2 decimal places. I know I could round this using a formula in Excel, but I really wanted to do it from my VB code.

Any thoughts?

Thanks Jerid

Posted by Jerid on June 18, 2001 7:27 AM

Problem Solved

I figured out a way to do it by looking at some old postings from Dave Hawley. Thanks Dave.

I changed the line that does the division to:
ActiveCell.Value = Application.WorksheetFunction.Dollar(ActiveCell.Offset(0, 10).Value / ActiveCell.Offset(0, 8).Value, 2)

Just incase anyone was interested.