Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

format decimal places in cell value - vba

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

  • 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.
    Code:
    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

  • #2
    Re: format decimal places in cell value - vba

    How about incorporating the ROUND function?
    Code:
    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!

    Comment


    • #3
      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?

      Comment


      • #4
        Re: format decimal places in cell value - vba

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

        Try using
        Code:
        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!

        Comment


        • #5
          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.

          Comment


          • #6
            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

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

            Regards,

            Mark.

            Comment


            • #7
              Re: format decimal places in cell value - vba

              Thanks Mark, that has resolved my problem

              John

              Comment

              Trending

              Collapse

              There are no results that meet this criteria.

              Working...
              X