Return the number to at least 2 decimal places.

Qj123

New Member
Joined
Aug 27, 2014
Messages
2
Hi all,

Is there possible by using VBA to convert to number to at least 2 decimal places if the original number is an integer or only 1 decimal place and remain unchanged if more than 2 decimal places.

Some Example:

[TABLE="width: 167"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Before[/TD]
[TD][/TD]
[TD]After[/TD]
[/TR]
[TR]
[TD="align: right"]10.99[/TD]
[TD][/TD]
[TD="align: right"]10.99[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD="align: right"]15.00[/TD]
[/TR]
[TR]
[TD="align: right"]65.777[/TD]
[TD][/TD]
[TD="align: right"]65.777[/TD]
[/TR]
[TR]
[TD="align: right"]187.3[/TD]
[TD][/TD]
[TD="align: right"]187.30[/TD]
[/TR]
[TR]
[TD="align: right"]123.3686[/TD]
[TD][/TD]
[TD="align: right"]123.3686[/TD]
[/TR]
[TR]
[TD="align: right"]70[/TD]
[TD][/TD]
[TD="align: right"]70.00[/TD]
[/TR]
</tbody>[/TABLE]

Thank a lot.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
[TABLE="class: multi_font_table, width: 90%"]
<TBODY>[TR]
[TD]Format(210.6, "#,##0.00")[/TD]
[TD]would return '210.60'[/TD]
[/TR]
[TR]
[TD]Format(210.6, "Standard")[/TD]
[TD]would return '210.60'[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
Is there possible by using VBA to convert to number to at least 2 decimal places if the original number is an integer or only 1 decimal place and remain unchanged if more than 2 decimal places.

There are two separate issue; not sure which you mean.

In VBA:
Format(number,"0.00############")
range.NumberFormat = "0.00############"

In Excel, Custom format 0.00############.

You need to decide the max decimal places. That allows for up to 14 decimal places total.
 
Upvote 0
There are two separate issue; not sure which you mean.

In VBA:
Format(number,"0.00############")
range.NumberFormat = "0.00############"

In Excel, Custom format 0.00############.

You need to decide the max decimal places. That allows for up to 14 decimal places total.

Yes i asking formula to solve both 3 scenario below:

1) integer to 2 decimal place (ex 1 to 1.00)
2) number with 1 decimal place to 2 decimal place (ex 1.1 to 1.10)
3) number > 2 decimal place remain unchanged (ex 1.1111 to 1.1111)

By using this

Format(number,"0.00############")
range.NumberFormat = "0.00############"

It solve my problem.
 
Upvote 0

Forum statistics

Threads
1,223,566
Messages
6,173,103
Members
452,501
Latest member
musallam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top