.NumberFormat question

[Rodger]

I'm sorry if this is basic, but I can't get the right code here (and I have reasoned thru much more complicated things than this, but alas ...)
I am trying to set a column of numbers to 2 decimal places, unless it is a zero, where I just want a "0" with no decimal places. I can do it with a formula to = "0", but it takes it as a text string, and when I sort the column from largest to small, it puts that "0" at the top.

Trying with a VBA loop.
What am I doing wrong? It keeps flagging the NumberFormat line, no matter what I try.

Cheers,

Rodger

Dim rng As Range
    Dim rcell As Range
    Set rng = Range("M8:M108")
   
    For Each rcell In rng
    If rcell = 0 Then
      With rcell
      .NumberFormat = "0"
      End With
    End If
    Next rcell

 

[Chihiro]

Try this.

.NumberFormat = "0.00;0.00;0"

 

[Rodger]

Hi Chihiro;

thx for the reply.

It still flags that line tho
.NumberFormat = "0.00;0.00;0"

 

[NARAYANK991]

Hi ,

One possibility is that you are not using the English language version of Excel. If so , see this link for information on issues related to internationalization :

http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm

Another option is to manually format any cell for the format that you want , and in the Immediate window , type in the following :

?ActiveCell.NumberFormatLocal

and see what is displayed. Use this same format string in your code , using .NumberFormatLocal instead of .NumberFormat

Narayan

 

[Rodger]

Thanks Narayan,
not quite the answer, but it pointed my head in the right direction.
I am using English Excel. Your answer led me to the correct solution.
Thanks again for your time on this one, as with others I have been stumped on.
Keep being amazing.
Cheers,
Rodger