VBA Remove decimal places if they don't exist

[RockandGrohl]

Hi fellas, quick one I hope.

Is there a non Conditional Formatting way of representing text like this:

£299
£189.99
£250
£399.99


So if the number has a decimal place, give it one, otherwise don't display it.

Basically I don't want to see this: £299.00

Cheers!

 

[Alex Blakenburg]

I think the closest you are going to get is to use a custom format where you change the fixed decimal place holders from .00 to .??
eg from [$£-en-GB]#,##0.00 to [$£-en-GB]#,##0.?? I am in Australia in the UK you might only see the £ symbol.
Unfortunately it will still show the decimal point even when its .00
eg £299. & £250.

 

[RockandGrohl]

In that case I may have to look in to an evaluation function where it decides how to format on a case-by-case basis.

 

[diddi]

you asked for a VBA solution, so you could just choose which format you need, row by row using

if Int(MyValue) = MyValue then
    Cells(  ,  ) = Format(MyValue , "£0")
else
    Cells(  ,  ) = Format(MyValue, "£0.00")
end if

 

[RockandGrohl]

you asked for a VBA solution, so you could just choose which format you need, row by row using

if Int(MyValue) = MyValue then
    Cells(  ,  ) = Format(MyValue , "£0")
else
    Cells(  ,  ) = Format(MyValue, "£0.00")
end if

Hi bud, I have this:

Range("AD2").Activate
x = 1

Dim rng As Range
Dim cl As Range

For x = 1 To 10

    Set rng = Range(Cells(2, ActiveCell.Column), Cells(lastrow, ActiveCell.Column))
   
    For Each cl In rng
    If Int(cl) Then
    cl.NumberFormat = "£0"
    Else
    cl.NumberFormat = "£0.00"
    End If
    Next cl
ActiveCell.Offset(0, 6).Activate
Next x

Which is built around your logic.

We have price in 10 columns spaced every 6 columns apart. So I'm hoping to just loop 10 times to change the whole column.

This does happily convert whole integers into a currency format with no decimal, but it also changes £269.99 to £270 which is no good.

Any help? Thanks.

EDIT: Nevermind, I missed the part of where your formula basically says "If Integer of £229.99 = £229.99 then"

So I've got it with the below:

For x = 1 To 10

    Set rng = Range(Cells(2, ActiveCell.Column), Cells(lastrow, ActiveCell.Column))
    
    For Each cl In rng
    If Int(cl) = cl Then
    cl.NumberFormat = "£0"
    Else
    cl.NumberFormat = "£0.00"
    End If
    Next cl
ActiveCell.Offset(0, 6).Activate
Next x

Thanks!

 

[diddi]

try this

if cl.value - Int(cl.value)<.0001 then

 

[Alex Blakenburg]

I would suggest that the values will look much better if they line up and have commas in for thousands and to do that:

In the IF being TRUE, I would replace the
cl.NumberFormat = "£0"
with
cl.NumberFormat = "£#,##0_._#_#"

and if you want £250.50 to appear as £250.5
then if the IF being FALSE replace
cl.NumberFormat = "£0.00"
with
cl.NumberFormat = "£#,##0.??"

 

[RockandGrohl]

Hi Alex,

Appreciate the input but that's not necessary, the output for this is a mail-merged document so the mail merge takes the explicit cell value as it appears, which is why I need something with the pound sign and not just the number format. We are offering products to customers and the price must say "£399.99" or "£280" - having something like £275.5" is not proper punctuation and then having gaps or false spaces causes more problems. Cheers.

 

[Alex Blakenburg]

Hi Alex,

Appreciate the input but that's not necessary, the output for this is a mail-merged document so the mail merge takes the explicit cell value as it appears, which is why I need something with the pound sign and not just the number format. We are offering products to customers and the price must say "£399.99" or "£280" - having something like £275.5" is not proper punctuation and then having gaps or false spaces causes more problems. Cheers.

Thanks for the update.