Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Thread: format decimal places in cell value - vba

1. I agreed to these rules
Join Date
11th July 2005
Location
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. 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

3. I agreed to these rules
Join Date
11th July 2005
Location
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. 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)"

```

Alastair

5. I agreed to these rules
Join Date
11th July 2005
Location
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. Member
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. I agreed to these rules
Join Date
11th July 2005
Location
Posts
13

Re: format decimal places in cell value - vba

Thanks Mark, that has resolved my problem

John

Excel Video Tutorials / Excel Dashboards Reports

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

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