vba NumberFormat do not put decimal if whole number

[CatyH]

I've read a few posts but none that answer the question for how to not display the decimal point:

Two scenarios: 100.3897364 and 102 should appear as:
100.39 and 102

but this format shows 100.39 and 102. (with the decimal).

How do I keep the decimal from appearing when a whole number?



.Range("I17").NumberFormat = "####0.##" --> 100.39 [happy with this]
.Range("I17").NumberFormat = "####0.##" --> 102. [do not want to display the .]

This is just for formatting - I do not want to change the actual values of the cells... thanks!

 

[JoeMo]

Maybe:

With ActiveSheet
If .Range("I17").Value <> Int(.Range("I17").Value) Then
    .Range("I17").NumberFormat = "0.00"
Else
    .Range("I17").NumberFormat = "0"
End If
End With

 

[CatyH]

Thanks for the fast reply... I'm getting an error though... probably because I have an actual range instead of a single cell...

With Worksheets(LaborGridws)
    .Range("I17:AB66").Formula = "=SUMIFS(Labor_List!$H:$H,Labor_List!$A:$A,1,Labor_List!$C:$C,I$15,Labor_List!$B:$B,$C17)"
    .Range("I17:AB66").Value2 = .Range("I17:AB66").Value2
    .Range("I17:AB66").NumberFormat = "####0.##"
    
    If .Range("I17:AB66").Value2 <> Int(.Range("I17:AB66").Value2) Then
        .Range("I17:AB66").NumberFormat = "0.00"
    Else
        .Range("I17:AB66").NumberFormat = "0"
    End If

End With

Maybe:

With ActiveSheet
If .Range("I17").Value <> Int(.Range("I17").Value) Then
    .Range("I17").NumberFormat = "0.00"
Else
    .Range("I17").NumberFormat = "0"
End If
End With

 

[Rick Rothstein]

Thanks for the fast reply... I'm getting an error though... probably because I have an actual range instead of a single cell...

With Worksheets(LaborGridws)
    .Range("I17:AB66").Formula = "=SUMIFS(Labor_List!$H:$H,Labor_List!$A:$A,1,Labor_List!$C:$C,I$15,Labor_List!$B:$B,$C17)"
    .Range("I17:AB66").Value2 = .Range("I17:AB66").Value2
    .Range("I17:AB66").NumberFormat = "####0.##"
    
    If .Range("I17:AB66").Value2 <> Int(.Range("I17:AB66").Value2) Then
        .Range("I17:AB66").NumberFormat = "0.00"
    Else
        .Range("I17:AB66").NumberFormat = "0"
    End If
End With

Does this work for you...

[table="width: 500"]
[tr]
	[td]With Worksheets(LaborGridws)
    .Range("I17:AB66").Formula = "=SUMIFS(Labor_List!$H:$H,Labor_List!$A:$A,1,Labor_List!$C:$C,I$15,Labor_List!$B:$B,$C17)"
    .Range("I17:AB66").Value2 = .Range("I17:AB66").Value2
    .Range("I17:AB66").NumberFormat = Evaluate("IF(" & .Name & "!I17:J18=INT(" & .Name & "!I17:J18),""0"",""0.00"")")
End With[/td]
[/tr]
[/table]

 

[CatyH]

Does this work for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]...
    .Range("I17:AB66").NumberFormat = Evaluate("IF(" & .Name & "!I17:J18=INT(" & .Name & "!I17:J18),""0"",""0.00"")")...[/TD]
[/TR]
</tbody>[/TABLE]

Hmm... I tried it at first as is (with the "I17:J18") thinking that might just test out a subset of the desired range. but ... error.

So I expanded the range instead of I17:J18 I made it be the full thousand cells... "I17:AB66". (or Maybe I'm not understanding what "Evaluate" formula does?)

At any rate... after 10 minutes I had to CTRL-ALT-DEL to stop Excel because it was not responding. Not sure if it was truly and error of if it was just processing a thousand cells in 10+ minutes. But this is only a very minor component of a much more complicated system and not worth investing large amounts of user-time for a format. So don't want the solution to take that long, either.

I think what I'm looking for is "optional text" in the way there are "optional numerical place values" (with the ## instead of 00) ... Making the "." optional... I believe there are optional strings using @ (?) or maybe *?) but can you _specify_ an optional string?

Appreciate your efforts here, just not seeing a solution yet. Thanks!

 

[Rick Rothstein]

Hmm... I tried it at first as is (with the "I17:J18") thinking that might just test out a subset of the desired range. but ... error.

Yes, that was a test case that I forgot to expand. As for the time factor... maybe Excel is doing a cell-by-cell physical update. See if this code works better (and correctly)...

  Application.ScreenUpdating = False
  On Error GoTo Whoops
  With Worksheets(LaborGridws)
    .Range("I17:AB66").Formula = "=SUMIFS(Labor_List!$H:$H,Labor_List!$A:$A,1,Labor_List!$C:$C,I$15,Labor_List!$B:$B,$C17)"
    .Range("I17:AB66").Value2 = .Range("I17:AB66").Value2
    .Range("I17:AB66").NumberFormat = Evaluate("IF(" & .Name & "!I17:AB66=INT(" & .Name & "!I17:AB66),""0"",""0.00"")")
  End With
Whoops:
  Application.ScreenUpdating = True

 

[CatyH]

... As for the time factor... maybe Excel is doing a cell-by-cell physical update....

OHHHH!!!!!!

I was thinking if this incorrectly... ~! THANK YOU for your code!

1) Your code works really well - it IS slow and not what I want the user to go through.... but...
2) I can run the 6.5 minutes per 1000 cells (I have five of these blocks, so half an hour to run it...) and then...
3) THE FORMATS ARE ALREADY APPLIED... the USER doesn't have to do this... the macro they run is only paste values!

I don't need to re-specify the formats when they run their macro - I'd only need to re-specify formats if something needed to be reset! Sweet!

This IS going to work, afterall....!

So... THANK YOU!!!!!!

 

[Rick Rothstein]

3) THE FORMATS ARE ALREADY APPLIED... the USER doesn't have to do this... the macro they run is only paste values!

I don't need to re-specify the formats when they run their macro - I'd only need to re-specify formats if something needed to be reset! Sweet!

Correct, it is the difference between retrofitting existing data and processing new data... what needs to be done to existing data does not have to be redone unless individual values change and then only those new values needs to (possibly) be reformatted.