Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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
    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. #2
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,256

    Re: Rounding Decimal Places In Vba

    VB:
    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,913

    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. #4
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,256

    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. #5
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,913

    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. #6
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,256

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


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, 09:57
  2. Consistent Decimal Places
    By davecurtis in forum EXCEL HELP
    Replies: 11
    Last Post: December 1st, 2006, 04:06
  3. Round to More Than 30 Decimal Places
    By x_paddypaws_x in forum EXCEL HELP
    Replies: 2
    Last Post: October 16th, 2006, 16:40
  4. Decimal Places
    By kjrae in forum EXCEL HELP
    Replies: 7
    Last Post: August 16th, 2005, 21:50
  5. IF and decimal places
    By ston821123 in forum EXCEL HELP
    Replies: 4
    Last Post: July 1st, 2005, 16: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