Number Format: Whole number without decimal separator

[JulienW]

Hello,

This problem seems dead simple but I can't find a way to solve it...

I'm looking for a format that will round the numbers with more than 2
decimals and not display the decimal separator when the number is an
integer:

2,222 -> 2,22
2 -> 2 (not 2, )
00,0200 -> 0,02

The best I've managed to get is 0,## but integers still appears with the
decimal separators (2 -> 2,)

Any idea to get around that?

Thanks,
Julien

[Ron Rosenfeld]

On Fri, 22 Feb 2002 23:01:22 +0000, JulienW <j.wie...@netcourrier.com>
wrote:

Well, it seems to me that you are looking to have two different number
formats depending on whether or not the number is an integer.

I don't think you can do that with simple number formatting. I believe you
need an event macro to do it.

The following event macro will do what you describe for all the cells in
the workbook and in US format. It also requires that you only select one
cell at a time.

I'm not sure of the best way to change it for your international format but
I'm sure it can be done.

To enter the macro, Tools/Macros/Visual Basic Editor

Under your current project in the Project Explorer Window, double click on
This WorkBook and paste the code into the window that opens up.

This should get you started.

=====================
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Excel.Range)

Application.EnableEvents = False

If IsNumeric(Target.Value) Then

If Int(Target.Value) = Target.Value Then
Target.NumberFormat = "0"
Else
Target.NumberFormat = "0.00"
End If

End If

Application.EnableEvents = True
End Sub
=========================
--ron

[JulienW]

Hi Ron,

Thanks for your answer.

The macro event solution is ok for constant but unfortunately it doesn't
work perfectly when the value is the result of a formula.

I just find quite weird that Excel always leave the decimal separator unless
you explicitely specify that the format is an "integer format".

thanks anyway
Julien

ps: the way you've written your macro work perfectly as VBE "translate" the
decimal separator when he exports data to excel.

[Ron Rosenfeld]

On Sat, 23 Feb 2002 09:53:52 +0000, JulienW <j.wie...@netcourrier.com>
wrote:

>The macro event solution is ok for constant but unfortunately it doesn't
>work perfectly when the value is the result of a formula.

I'm not sure what the problem is but there are a few possibilities.

The macro, as written, will only work on the Target cell.

So if, for example in D3 you have a formula =A1/B1, changing something in
A1 will not affect the formatting of D3.

If this is your problem, and depending on your application, I see several
solutions.

Certainly, you could check for dependents of the Target cell, and alter the
number format accordingly. You'd need to do some checking to see if there
are dependents, and then cycle through the range.

Another solution would be to always be checking a certain range for the
desired number format. For a large range (and a slow computer) this might
take some time, though.

If your problem with formulas is different than I describe, let me know and
I'll see what I can think of.

Oh, I'm glad to hear that VBA is smart enough to translate my macro! I
have absolutely NO experience with changing the regional settings.

--ron

[JulienW]

>
>> The macro event solution is ok for constant but unfortunately it doesn't
>> work perfectly when the value is the result of a formula.
>
> I'm not sure what the problem is but there are a few possibilities.
>
> The macro, as written, will only work on the Target cell.
>
> So if, for example in D3 you have a formula =A1/B1, changing something in
> A1 will not affect the formatting of D3.
>
> If this is your problem, and depending on your application, I see several
> solutions.
>
> Certainly, you could check for dependents of the Target cell, and alter the
> number format accordingly. You'd need to do some checking to see if there
> are dependents, and then cycle through the range.
>
> Another solution would be to always be checking a certain range for the
> desired number format. For a large range (and a slow computer) this might
> take some time, though.
>
> If your problem with formulas is different than I describe, let me know and
> I'll see what I can think of.

Thanks for your clever solutions. I should be able to implement them myself,
but the speed of my computer is critical indeed. The worbook is really huge
and it will probably loose alot of "reactivity". However I'll still try to
implement your solution when I'll be done with my project.

> Oh, I'm glad to hear that VBA is smart enough to translate my macro! I
> have absolutely NO experience with changing the regional settings.

Yeap clever VBA isn't it?
There's no real trick about regional settings. Stuffs like
range("A1").formula = "=Round(A2,0)" are automatically translated in the
"Excel regional language" when pasted into the worksheets and vice versa
(including all separators , ; : . ...)

In fact as long as excel has evaluate a data it's fine. Problems can arise
when the user is asked to input data (for example in a userform or an
inputbox). Then it is safer to make sure that the data are in "English" if
they are going to be used in VBE and in the regional language if they are
going to be used in the worksheets.
Not sure if I'm understandable but that's how it works.

thanks for your answers
Julien

[Alan-SouthAust]

Julien
=IF(MOD(A1,1)=0,TEXT(A1,"0"),TEXT(A1,"0.00")) will return
you numbers in text format,so if you want to use these
later as values you would have to put a value statement in
your calculating formulae.

Alan

>.
>

[JulienW]

> Julien
> =IF(MOD(A1,1)=0,TEXT(A1,"0"),TEXT(A1,"0.00")) will return
> you numbers in text format,so if you want to use these
> later as values you would have to put a value statement in
> your calculating formulae.
>
> Alan

Hi Alan,
Wow another solution!
I don't know exactly what you mean by A1. If it is the cell where the user
enter a data, I think it's going to be a bit confusing as the value entered
by the user will be displayed in a different cells.
However it's a good way to deals with result from formula.

So there we are, I think that for large worbook with lot of linked cells a
good way to deal with the problem (regarding the calculation speed) is to
use the WorkBook_SheetChange procedure (from Ron) for the value entered by
the user. And use Alan's formula for the formula (ie for a given cell,
replace its formula by Alan's formula not forgetting to replace A1 by the
previous formula... well ok, no one has understand what I mean but I'm sure
you will guess...). That way we don't need a SheetCalulate procedure and
probably save alot of computation time :)

Cool, thanks to both of you
Julien