numeric format

S

scalpa

hi

Please how to set the numeric format of cells which can contain different
types of numbers like :
123456,580 or 123
the number could be an integer so in this case i don't want to see the coma
of an decimal part which does'nt exist : if the number is 123 i don't want
123.
I want to see long numbers with the thousand separator :123 456, 58
I don't want to show the useless 0 : 123 456,58

I tried plenty of ### ###.# (etc.) and never found the solution that works
for all the cases

thank for your help

http://www.scalpa.info
 
B

Bernard Liengme

Bon jour M. Scalpa:

You can make a Custom Format to have a space as the thousands separator: ###
###,00
will show 123 450,58

There is no way with formatting to drop 0s at the end. You call these
'useless' but, as a scientist, 12,560 and 12,56 have different meanings to
me. The first means I know the measurement to ±0,001 units while the second
is less precise ±0,01. We call this stuff "significant figures" but the term
is often misunderstood to mean "how many decimals"

Nor can you have a format in one cell that shows integers with no decimals
and real numbers with (say) 2 decimals

best wishes
 
P

Pascal

--
bonjour

You can make a Custom Format to have a space as the thousands separator:
### ###,00
will show 123 450,58

yes : but when the number is 123 450 it will show 123 450,00
There is no way with formatting to drop 0s at the end. You call these
'useless' but, as a scientist, 12,560 and 12,56 have different meanings to
me. The first means I know the measurement to ±0,001 units while the
second
is less precise ±0,01. We call this stuff "significant figures" but the
term
is often misunderstood to mean "how many decimals"
Click to expand...
yes but the purpose is to train pupils to find other numbers between to
given numbers : (i hope the gif file beneath will be kept in the post...)

25922
25922.1












so i don't want to give them a clue with 25922,0
So the best i get now is to let the format of cells set to "standard" but i
loose the thousands separator... Arrgh !
thanks
 
R

Ron Rosenfeld

hi

Please how to set the numeric format of cells which can contain different
types of numbers like :
123456,580 or 123
the number could be an integer so in this case i don't want to see the coma
of an decimal part which does'nt exist : if the number is 123 i don't want
123.
I want to see long numbers with the thousand separator :123 456, 58
I don't want to show the useless 0 : 123 456,58

I tried plenty of ### ###.# (etc.) and never found the solution that works
for all the cases

thank for your help

http://www.scalpa.info
Click to expand...

You can only do this using VBA and an event triggered macro. There is no
individual format that will do what you request, so you have to change the
format depending on the value in the cell.

If you will be entering both numbers and formulas into the range, you will have
to run this macro on every calculation. (It will run automatically). Note that
with regard to calculations, rounding errors may sometimes give unexpected
results.

If you will only be entering numbers, then the macro should be changed.

To enter this macro:

Right click on the sheet tab and select View Code
Paste the code below into the window that opens.
Change the range for AOI to refer to your area of interest on the worksheet.

=============================================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim c As Range
Const FmtNoDec As String = "# ###"
Const Fmt2Dec As String = "# ##0.00"

Set AOI = [A1:A10] 'set range for this custom format
On Error Resume Next
For Each c In AOI
If Int(c.Value) = c.Value Then
c.NumberFormat = FmtNoDec
Else
c.NumberFormat = Fmt2Dec
End If
Next c

End Sub
=====================================================
--ron
 
P

Pascal

You can only do this using VBA and an event triggered macro. There is no
individual format that will do what you request, so you have to change the
format depending on the value in the cell.
Click to expand...

do you know a good place to learn vba on internet with all the methods,
properties, events well explained ?
If you will only be entering numbers, then the macro should be changed.
Click to expand...
in some place there are formulas like : SI(DO1<(DO2*2);(2*DO2)+DO1;DO1)
To enter this macro:

Right click on the sheet tab and select View Code : cooler than alt +F11

=============================================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim c As Range
Const FmtNoDec As String = "# ###"
Const Fmt2Dec As String = "# ##0.00"

Set AOI = [A1:A10] 'set range for this custom format
On Error Resume Next
For Each c In AOI
If Int(c.Value) = c.Value Then
c.NumberFormat = FmtNoDec
Else
c.NumberFormat = Fmt2Dec
End If
Next c

End Sub
=====================================================
Click to expand...
Thanks for spending time on this.

I ve got some problems for changing the ranges: i have 4 "named ranges" :
sélection1 , sélection2, sélection3, sélection4. I can call them with a
button. like this :
#####################
Private Sub CommandButton1_Click()
Call Sel1
End Sub
######################
and in a module :
#########################
Sub Sel1()
'
' Macro enregistrée le 06/03/2007 par scalpa
'
Range("sélection1").Select
End Sub
##########################

so i tried : Set AOI = "sélection1" & "sélection2" etc... without success
=============================================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim c As Range
Const FmtNoDec As String = "# ###"
Const Fmt2Dec As String = "# ##0.00"

Set AOI = "sélection1" & "sélection2" & "sélection3" & "sélection4"
On Error Resume Next
For Each c In AOI
If Int(c.Value) = c.Value Then
c.NumberFormat = FmtNoDec
Else
c.NumberFormat = Fmt2Dec
End If
Next c

End Sub
=====================================================
Click to expand...

what's wrong please ?
thanks
pascal
 
R

Ron Rosenfeld

so i tried : Set AOI = "sélection1" & "sélection2" etc... without success

what's wrong please ?
Click to expand...

Since AOI is a Range, you have to set it equal to a range and not to a
concatenated string.

For example, if all of your Named Ranges are on the same worksheet:

Set AOI = Range("Selection1, Selection2, Selection3, Selection4")

See "Referring to Multiple Ranges" in VBA Help

------------------------------------------
do you know a good place to learn vba on internet with all the methods,
properties, events well explained ?
Click to expand...

No I don't. I'm mostly self-taught, using old knowledge and the Help facility,
and this NG.
--ron
 
P

Pascal

I tried the new code : but it has no effect in the cells referenced by the
different "sélections" !!!! arrrghh!
########################' in the worksheet
Option Explicit

Private Sub CommandButton1_Click()
Call Sel1
End Sub
Private Sub CommandButton2_Click()
Call Sel2
End Sub
Private Sub CommandButton3_Click()
Call Sel3
End Sub
Private Sub CommandButton4_Click()
Call Sel4
End Sub
Private Sub CommandButton5_Click()
Call Sel1
Call Sel2
Call Sel3
Call Sel4
End Sub

Private Sub CommandButtonTout_Click()
Call Tout
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim c As Range
Const FmtNoDec As String = "# ###"
Const Fmt2Dec As String = "# ##0.00"

Set AOI = Range("Selection1, Selection2, Selection3, Selection4") '[A1:A10]
'set range for this custom format
On Error Resume Next
For Each c In AOI
If Int(c.Value) = c.Value Then
c.NumberFormat = FmtNoDec
Else
c.NumberFormat = Fmt2Dec
End If
Next c

End Sub

##################################### ' in the module1
Option Explicit

Sub Sel1()
'
' Macro enregistrée le 06/03/2007 par scalpa
'
Range("sélection1").Select
End Sub

Sub Sel2()
'
' Macro enregistrée le 06/03/2007 par scalpa
'
Range("sélection2").Select
End Sub
Sub Sel3()
'
' Macro enregistrée le 06/03/2007 par scalpa
'
Range("sélection3").Select
End Sub
Sub Sel4()
'
' Macro enregistrée le 06/03/2007 par scalpa
'
Range("sélection4").Select
End Sub
#####################################
But when i try this in a new work sheet where the selections refer to
individual cells: for example sélection1 = cell A1 etc... It works !
I change a bit the code to :

Private Sub Worksheet_Calculate()' instead of Private Sub Worksheet_Change()
Dim AOI As Range
Dim c As Range
Const FmtNoDec As String = "### ### ### ###"
Const Fmt2Dec As String = "### ### ### ###.###"

Set AOI = Range("sélection1, sélection2, sélection3")
On Error Resume Next
For Each c In AOI
If Int(c.Value) = c.Value Then
c.NumberFormat = FmtNoDec
Else
c.NumberFormat = Fmt2Dec
End If
Next c
End Sub
#################################
So i think perhaps the code of the differents ranges are misunderstood by
the sub ? what do you think ?
pascal
 
R

Ron Rosenfeld

#####################################
But when i try this in a new work sheet where the selections refer to
individual cells: for example sélection1 = cell A1 etc... It works !
I change a bit the code to :

Private Sub Worksheet_Calculate()' instead of Private Sub Worksheet_Change()
Dim AOI As Range
Dim c As Range
Const FmtNoDec As String = "### ### ### ###"
Const Fmt2Dec As String = "### ### ### ###.###"

Set AOI = Range("sélection1, sélection2, sélection3")
On Error Resume Next
For Each c In AOI
If Int(c.Value) = c.Value Then
c.NumberFormat = FmtNoDec
Else
c.NumberFormat = Fmt2Dec
End If
Next c
End Sub
#################################
So i think perhaps the code of the differents ranges are misunderstood by
the sub ? what do you think ?
pascal
Click to expand...

Worksheet_Change() should trigger more often than worksheet_calculate.

Could it be that the separator, in VBA in your language, should be other than a
comma ","?

Also, in the format string, there is no need for it to be so long. # ### and
# ##0.00 are more than long enough

Be sure the routine is in a worksheet module, and not in a regular module.


--ron
 
H

Harlan Grove

scalpa said:
Please how to set the numeric format of cells which can contain
different types of numbers like: 123456,580 or 123
the number could be an integer so in this case i don't want to see
the coma of an decimal part which does'nt exist: if the number is
123 i don't want 123.
I want to see long numbers with the thousand separator: 123 456, 58
I don't want to show the useless 0 : 123 456,58
Click to expand...
....

If you could live with always showing the decimal comma, you could use
the number format

# ##0,???

If you could live with the displayed numbers being stored as text and
displayed in a monospace typeface with right horizontal alignment, use
the formula

=TEXT(x;IF(INT(x)=x;"# ##0_ _ _ _ ";"# ##0,???"))

where x is a placeholder for a given numberic value.
 
Top