Two decimal numbers only

[Mhabashy]

Hello every one,
i need to write a code to be as a private sub for a sheets, to have only two decimal numbers if the number has more than two decimal numbers not all numbers
iam using the following formula:

Sub bbbb()
 Range("A18:C30").NumberFormat = "0.00"
 Range("D3:D14").NumberFormat = "0.00"
End Sub

the problem its converted even the single number which was without decimel ex (25.00) i need it to be as its (25)

i need the result to be for the only if there is decimal number and the code to work automatic in the sheet like private sub

thanks for your support,

[AliGW]

Please don't SHOUT at us (all CAPS is considered as shouting online, and is very hard to read).

Kindly replace the text in your post with sentence case. You can do this by pasting the text into Word, changing it there and then copying back.

Thank you.

[Mhabashy]

Sorry
Please accept my apologies,

[AliGW]

Much better - I can read it now. Thank you.

[MrShorty]

IME, exactly what you are asking for cannot be achieved with number formatting alone. If you can tolerate the decimal point being displayed with integers (25. instead of 25), then the same number format using # or ? as the placeholder instead of 0 will work: .Numberformat="#.##" or .NumeberFormat="?.??"

If you cannot tolerate seeing the decimal point with integers, then you will need conditional formatting or a block If to choose the number formatting based on a test of whether the cell contains an integer or not. Something like (using VBA to test the condition and choose the number format):

Set mycell=Range...
If Int(mycell.Value)=mycell.value then
mycell.NumberFormat="0"
Else
mycell.NumberFormat="0.##"
End If

help file explaining the meaning of placeholders in number format codes: https://support.microsoft.com/en-us/...rs=en-us&ad=us
help file for conditional formatting (see the section on using a formula to choose how to format cells): https://support.microsoft.com/en-us/...b-f1951ff89d7f

Does that help?

[Mhabashy]

MrShorty thanks for your reply , but the code is not working can you check again please.
its not accepting

Set mycell=Range...

iam doing

Sub ccc()
Set mycell = Range(J4, V12)
If Int(mycell.Value) = mycell.Value Then
mycell.NumberFormat = "0"
Else
mycell.NumberFormat = "0.##"
End If
End Sub

run time error 1004

[ByteMarks]

Could use conditional formatting

[Mhabashy]

I tried the conditional formatting but its not give me what I need and actually its 100s of sheets so its difficult to work with conditional formatting its better if there is VBA and to copy and baste for all sheets
I need the VBA to check the number and to change the decimal to be two only if there are more than 2 EX:

5.24568 = 5.25
5.24368 = 5.24
5.24 = 5.24
5 = 5

[AliGW]

5.24 = 5.24

This is NOT more than 2 - it's more than 1. So which is it that you want?

[Mhabashy]

If its 2 so keep it if its more so make it 2 (my max is 2).

[ByteMarks]

This formula produces the desire result with General formatting.
Could always implement it with VBA if you need to.

=IF(MOD(A1,1)=0,A1,ROUND(A1,2))

[Mhabashy]

Attachment 877503

I tried the formula but some changed and some not

=IF(MOD(A1,1)=0,A1,ROUND(A1,2))

[JeteMc]

Selecting attachment 877503 results in the following error message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".

[Sintek]

If you are wanting when the user enters a number into a specific cell and have the code change the number formatting automatically then you will need a sheet event...
what is the range required for this...Will help with a sample file showing before|after scenario though....