Announcement

Collapse
No announcement yet.

Rounding Decimal Places In Vba

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!

  • #2
    Re: Rounding Decimal Places In Vba

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

    Comment


    • #3
      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, 15:45.
      Reafidy

      Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

      Comment


      • #4
        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, 16:25.

        Comment


        • #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, 16:43.
          Reafidy

          Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

          Comment


          • #6
            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
            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, 15:25.

            Comment

            Working...
            X