Return the number to at least 2 decimal places.

[Qj123]

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:

Before		After
10.99		10.99
15		15.00
65.777		65.777
187.3		187.30
123.3686		123.3686
70		70.00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Thank a lot.

 

[Roderick_E]

Format(210.6, "#,##0.00")	would return '210.60'
Format(210.6, "Standard")	would return '210.60'

<TBODY>
</TBODY>

 

[joeu2004]

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.

 

[Qj123]

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.