Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Rounding Decimal Places In Vba

1. I agreed to these rules
Join Date
11th March 2007
Posts
4

## Rounding Decimal Places In Vba

I am retrieving information from Excel and sending it to Outlook via VBA. How can I adjust the number of decimal places that VBA rounds to
--I want 33.33
VB:
```strbody = ThisWorkbook.Sheets("Tracker").Range("b25").Value & vbNewLine & _
ThisWorkbook.Sheets("Tracker").Range("b26").Value & vbNewLine & _
ThisWorkbook.Sheets("Tracker").Range("b27").Value & vbNewLine & _
ThisWorkbook.Sheets("Tracker").Range("b28").Value & vbNewLine & _
ThisWorkbook.Sheets("Tracker").Range("b29").Value & vbNewLine & _
ThisWorkbook.Sheets("Tracker").Range("b30").Value & ThisWorkbook.Sheets("Tracker").Range("c30").Value & vbNewLine & _
ThisWorkbook.Sheets("Tracker").Range("b31").Value & ThisWorkbook.Sheets("Tracker").Range("C31").Value & ThisWorkbook.Sheets("Tracker").Range("D31").Value & vbNewLine & _
ThisWorkbook.Sheets("Tracker").Range("b32").Value

```
this is the code that i'm using. I need the
VB:
```Range(cell ref).Value

```
to be rounded to only 2 decimal places

Any help would be much appreciated!

Excel Video Tutorials / Excel Dashboards Reports

2. Excel Samurai
Join Date
19th July 2004
Location
Tokyo, Japan
Posts
9,423

## Re: Rounding Decimal Places In Vba

VB:
```worksheetFunction.Round(Range(cell ref).Value,2)

```

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: Rounding Decimal Places In Vba

Hi,
VB:
```Format(Range(cell ref).Value, "#.00")

```
also looking at your code you could use With
VB:
```With ThisWorkbook.Sheets("Tracker")
strbody = .Range("b25").Value & vbNewLine & _
.Range("b26").Value & vbNewLine & _
.Range("b27").Value & vbNewLine & _
.Range("b28").Value & vbNewLine & _
.Range("b29").Value & vbNewLine & _
.Range("b30").Value & .Range("c30").Value & vbNewLine & _
.Range("b31").Value & .Range("C31").Value & .Range("D31").Value & vbNewLine & _
.Range("b32").Value
End With

```
Last edited by Reafidy; March 18th, 2007 at 15:45.

Excel Video Tutorials / Excel Dashboards Reports

4. Excel Samurai
Join Date
19th July 2004
Location
Tokyo, Japan
Posts
9,423

## Re: Rounding Decimal Places In Vba

VB:
```With ThisWorkbook.Sheets("Tracker")
a = .Range("b25:b35").Value
End With
For i = 1 To UBound(a,1)
If IsNumeric(a(i,1)) Then a(i,1) = WorksheetFunction(a(i.1),2)
Next
msg = Join\$(Application.Transpose(Application.Transpose(a)),vbLf)
End Sub

```
Last edited by jindon; March 18th, 2007 at 16:25.

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Rounding Decimal Places In Vba

Jindon,

Did you allow for the range C30 and C31 and D31 amongst the code? I cant see it.

By the way I'm often impressed with the clever array formula's you come up with.
Last edited by Reafidy; March 18th, 2007 at 16:43.

Excel Video Tutorials / Excel Dashboards Reports

6. Excel Samurai
Join Date
19th July 2004
Location
Tokyo, Japan
Posts
9,423

## Re: Rounding Decimal Places In Vba

Originally Posted by Reafidy
Jindon,

Did you allow for the range C30 and C31 and D31 amongst the code? I cant see it.

By the way I'm often impressed with the clever array formula's you come up with.
No, I missed them
it should be
VB:
```Sub test()
Dim a, i As Long, ii As Integer
a = Range("b26:d32").Value
For i = 1 To UBound(a, 1)
For ii = 1 To UBound(a, 2)
If IsNumeric(a(i, ii)) Then
msg = msg & WorksheetFunction.Round(a(i, ii), 2)
Else
msg = msg & a(i, ii)
End If
y = vbTab
If ii = UBound(a, 2) Then y = vbLf
msg = msg & y
Next
Next
MsgBox msg
End Sub

```
Last edited by jindon; March 19th, 2007 at 15:25.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno