Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: format decimal places in cell value - vba

  1. #1
    Join Date
    11th July 2005
    Location
    Reading UK
    Posts
    13

    format decimal places in cell value - vba

    Please can someone tell me how I could change this code to display value with 1 decimal place.
    VB:
    Worksheets(t1dsheet).Cells(row, col).Value = "=(R[-53]C/R[-1]C)*100" 
    row = row + 1 
    
    
    The value currently displayed in the cell is 33.01684963 but I would like it to show 30.0

    thanks John

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    5th May 2005
    Location
    University of Kent!!, UK
    Posts
    988

    Re: format decimal places in cell value - vba

    How about incorporating the ROUND function?
    VB:
    Worksheets(t1dsheet).Cells(row, col).Value = "=Round((R[-53]C/R[-1]C)*100,-1)" 
    
    
    This will lose the accuracy though.

    Alastair
    Einstein:
    Things should be made as simple as possible, but not any simpler

    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  3. #3
    Join Date
    11th July 2005
    Location
    Reading UK
    Posts
    13

    Re: format decimal places in cell value - vba

    hmm, thats strange, the cell now displays Round((R[-53]C/R[-1]C)*100,-1)and hasnt made the calculation, any ideas?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    5th May 2005
    Location
    University of Kent!!, UK
    Posts
    988

    Re: format decimal places in cell value - vba

    Works perfectly for me. Does the cell have "=" at the front?

    Try using
    VB:
    Worksheets(t1dsheet).Cells(row, col).FormulaR1C1= "=Round((R[-53]C/R[-1]C)*100,-1)" 
    
    
    instead

    Alastair
    Einstein:
    Things should be made as simple as possible, but not any simpler

    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  5. #5
    Join Date
    11th July 2005
    Location
    Reading UK
    Posts
    13

    Re: format decimal places in cell value - vba

    ok that works thanks. I have changed the -1 to -.1 which seems to increases the accuracy a little.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th October 2004
    Posts
    40

    Re: format decimal places in cell value - vba

    If you don't want to lose accuracy you can use this line of code after you have inserted your original formula

    VB:
    Worksheets(t1dsheet).Cells(row, col).NumberFormat = "0.0" 
    
    
    This retains 33.01684963 but will simply display 33.0

    Regards,

    Mark.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    11th July 2005
    Location
    Reading UK
    Posts
    13

    Re: format decimal places in cell value - vba

    Thanks Mark, that has resolved my problem

    John

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Format All Percentage Cells To X Decimal Places
    By Hardway in forum EXCEL HELP
    Replies: 2
    Last Post: May 17th, 2008, 09:43
  2. Control Format Of Decimal Places In Code
    By moneyshot1 in forum EXCEL HELP
    Replies: 2
    Last Post: November 17th, 2007, 02:53
  3. VBA Increase The Decimal Places Of A Cell
    By novitaun in forum EXCEL HELP
    Replies: 2
    Last Post: July 27th, 2007, 23:03
  4. Replies: 5
    Last Post: April 27th, 2007, 20:13
  5. VBA: can you format decimal places for certain ranges
    By chick-racer in forum EXCEL HELP
    Replies: 1
    Last Post: November 7th, 2003, 07:19

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