Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Rounding Decimal Places In Vba

  1. #1
    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
    --Instead of 33.3333333333333
    Code:
          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
    Code:
    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. #2
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    9,423

    Re: Rounding Decimal Places In Vba

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,984

    Re: Rounding Decimal Places In Vba

    Hi,
    Code:
    Format(Range(cell ref).Value, "#.00")
    also looking at your code you could use With
    Code:
    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 16:45.

    Excel Video Tutorials / Excel Dashboards Reports


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

    Re: Rounding Decimal Places In Vba

    Code:
    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 17:25.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,984

    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 17:43.

    Excel Video Tutorials / Excel Dashboards Reports


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

    Re: Rounding Decimal Places In Vba

    Quote 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
    Code:
    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 16:25.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Fixed Decimal Places
    By naus in forum EXCEL HELP
    Replies: 3
    Last Post: June 6th, 2007, 10:57
  2. Consistent Decimal Places
    By davecurtis in forum EXCEL HELP
    Replies: 11
    Last Post: December 1st, 2006, 05:06
  3. Round to More Than 30 Decimal Places
    By x_paddypaws_x in forum EXCEL HELP
    Replies: 2
    Last Post: October 16th, 2006, 17:40
  4. Decimal Places
    By kjrae in forum EXCEL HELP
    Replies: 7
    Last Post: August 16th, 2005, 22:50
  5. IF and decimal places
    By ston821123 in forum EXCEL HELP
    Replies: 4
    Last Post: July 1st, 2005, 17:32

Bookmarks

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