allign on decimal point

  • Thread starter Thread starter Steve Gerdemann
  • Start date Start date
S

Steve Gerdemann

Is it possible to allign an excel work sheet so that
numbers like 32.2, 1.2 & .002 all align on the decimal
point

Thanks
Steve Gerdemann
 
Give the range a custom format like:

#,##0.??????

(as many trailing question marks as you want.)
 
Dave,

Wouldn't

Format/Cells/Number/Accounting

do what he was looking for without having to custom format??

John
 
I think it would depend on if the OP wanted extra 0's after the last digit:

32.200
1.200
0.002

(with no currency mark)

With the custom format (#,##0.???), I got:

32.2
1.2
0.002

I guess it depends on what Steve really wanted.
 
Dave,

Related question:

I would like to align

500
58,0
32.2
1.2
0.002

on the decimal point so the result will be

500
58
32.2
1.2
0.002
Hope even after sending over internet you will see what I mean; from top to
bottom the 0 the 8 the 2 the 1 and the leftmost 0 are in line.
The especially difficult part (for me) is that of integers not only zero's
to the right of the decemal point should not show, but also the decimal
point itself should not show.
With custom formats like (#,##0.???) I can't get rid of the decimal point in
case of integers.
Is possible what I want?

Jack Sons
The Netherlands
 
I don't think you can do this with numberformat.

(Am I right this time, Ken??? The last time I answered this way, I was thinking
about your situation--not the question in the post. And Ken (and Ron) corrected
me <<several times!!!>>. <vbg>)

Anyway, if these values are typed in, you could use a worksheet_change event
that resets the numberformat depending on what you typed.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If Target.HasFormula Then Exit Sub

If Application.IsNumber(Target.Value) Then
If CLng(Target.Value) = Target.Value Then
'whole number
Target.NumberFormat = "########0_._0_0_0"
Else
'decimal
Target.NumberFormat = "########0.???"
End If
Else
Target.NumberFormat = "General"
End If

End Sub

(Adjust those ##### to include commas if you want them. And change the range to
what you want. I used column A.)

If the values were the results of formulas, you could use the
worksheet_calculate event.

Right click on the worksheet tab that should have this behavior and select view
code. Paste this in and adjust the range/numberformats. Then back to excel to
test it out.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Jack said:
Dave,

Related question:

I would like to align

500
58,0
32.2
1.2
0.002

on the decimal point so the result will be

500
58
32.2
1.2
0.002
Hope even after sending over internet you will see what I mean; from top to
bottom the 0 the 8 the 2 the 1 and the leftmost 0 are in line.
The especially difficult part (for me) is that of integers not only zero's
to the right of the decemal point should not show, but also the decimal
point itself should not show.
With custom formats like (#,##0.???) I can't get rid of the decimal point in
case of integers.
Is possible what I want?

Jack Sons
The Netherlands
Click to expand...
 
Thaks Dave, I will try.

Jack.

Dave Peterson said:
I don't think you can do this with numberformat.

(Am I right this time, Ken??? The last time I answered this way, I was thinking
about your situation--not the question in the post. And Ken (and Ron) corrected
me <<several times!!!>>. <vbg>)

Anyway, if these values are typed in, you could use a worksheet_change event
that resets the numberformat depending on what you typed.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If Target.HasFormula Then Exit Sub

If Application.IsNumber(Target.Value) Then
If CLng(Target.Value) = Target.Value Then
'whole number
Target.NumberFormat = "########0_._0_0_0"
Else
'decimal
Target.NumberFormat = "########0.???"
End If
Else
Target.NumberFormat = "General"
End If

End Sub

(Adjust those ##### to include commas if you want them. And change the range to
what you want. I used column A.)

If the values were the results of formulas, you could use the
worksheet_calculate event.

Right click on the worksheet tab that should have this behavior and select view
code. Paste this in and adjust the range/numberformats. Then back to excel to
test it out.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Click to expand...
 
Dave,

I changed the decimal case as follows (
'decimal
Target.NumberFormat = "########0.??"
one ? less for only two decimals)

It appears that

30,004 results in 30, should be 30
30,4 results in 30,4 should be 30,40


If indeed the values were the results of formulas, should

If Target.HasFormula Then Exit Sub

be changed in a lead to another (new) part of this sub that is adapted to
work with the results of formulae and using worksheet_calculate event as ypu
said?

Please show me how the code has to be addapted to both these extra wishes
(sorry that I dit not state these aspects the first time).

Jack.
 
It sounds like you want to show the integer value if it would be an integer if
you rounded the value to 2 decimal places. If that's true, then try this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myValue As Double

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If Target.HasFormula Then Exit Sub

If Application.IsNumber(Target.Value) Then
myValue = Target.Value
If CLng(myValue) = myValue _
Or CLng(myValue) = Round(myValue, 2) Then
'whole number or rounds to a whole number
Target.NumberFormat = "######0_._0_0"
Else
'decimal
Target.NumberFormat = "######0.??"
End If
Else
Target.NumberFormat = "General"
End If

End Sub

And you'll have to tell the worksheet_calculate event what cells to look at.
(I'm assuming it's not the whole usedrange.)

Private Sub Worksheet_Calculate()

Dim myValue As Variant
Dim myRng As Range
Dim myCell As Range

Set myRng = Me.Range("c1:c99") 'or whatever you want.

For Each myCell In myRng.Cells
myValue = myCell.Value
If Application.IsNumber(myValue) Then
If CLng(myValue) = myValue _
Or CLng(myValue) = Round(myValue, 2) Then
'whole number or rounds to a whole number
myCell.NumberFormat = "######0_._0_0"
Else
'decimal
myCell.NumberFormat = "######0.??"
End If
Else
myCell.NumberFormat = "General"
End If
Next myCell

End Sub

Jack said:
Dave,

I changed the decimal case as follows (
'decimal
Target.NumberFormat = "########0.??"
one ? less for only two decimals)

It appears that

30,004 results in 30, should be 30
30,4 results in 30,4 should be 30,40

If indeed the values were the results of formulas, should

If Target.HasFormula Then Exit Sub

be changed in a lead to another (new) part of this sub that is adapted to
work with the results of formulae and using worksheet_calculate event as ypu
said?

Please show me how the code has to be addapted to both these extra wishes
(sorry that I dit not state these aspects the first time).

Jack.
Click to expand...
 
Dave,

Very kind that you helped again, I think I'm allmost there.

Two remaining questions:

First.

In both subs, when the result is for instance 30,4 it should be 30,40.
Because more precisely stated what I want is:
"If the number after rounding to two decimals is integer, it should show no
decimal point and no decimals (two zeros) right of the (not showing) decimal
point.
If the number after rounding to two decimals is not integer it should always
show two decimals, even if the rightmost decimal (second decimal) is zero.
All results vertically aligned at the decimal point, wether it shows or not"

Second.

You said:
Your second sub "Private Sub Worksheet_Calculate()" works through a for next
loop - which can take a lot of time for a large spreadsheet with a lot of
formulas, as is the case in my workbook - and the loop is triggered by the
calculate event. Can it be done fore all formula cells in the sheet in the
the same time, perhaps by something like

for each c in my....

or perhaps with something like

SpecialCells(xlCellTypeFormulas)

I would be very grateful if you clarify these last two points (missing zero
and all in one execution).

Jack.
 
the easy change:
Change:
Target.NumberFormat = "######0.??"
to:
Target.NumberFormat = "######0.00"

The .?? says reserve room for up to two decimals, but it won't always show them.

The more difficult one.

I think you're going to have to live with the pain of waiting for excel to loop
through the cells--but the number of cells could be reduced using specialcells
and even more looking for just numbers within that.

Private Sub Worksheet_Calculate()

Dim myValue As Variant
Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Me.Range("c1:c99") _
.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0

If myRng Is Nothing Then Exit Sub

For Each myCell In myRng.Cells
myValue = myCell.Value
If Application.IsNumber(myValue) Then
If CLng(myValue) = myValue _
Or CLng(myValue) = Round(myValue, 2) Then
'whole number or rounds to a whole number
myCell.NumberFormat = "######0_._0_0"
Else
'decimal
myCell.NumberFormat = "######0.00"
End If
Else
myCell.NumberFormat = "General"
End If
Next myCell

End Sub

=========
I'm not sure if this is an alternative for you, but maybe you could do all your
data entry and then run a macro (tools|macro|macros...) that does the
formatting.

It wouldn't be "realtime" and you'd have to remember to run it when you were
finished (well, each time you thought you were finished).

But it would be a one time shot.


Jack said:
Dave,

Very kind that you helped again, I think I'm allmost there.

Two remaining questions:

First.

In both subs, when the result is for instance 30,4 it should be 30,40.
Because more precisely stated what I want is:
"If the number after rounding to two decimals is integer, it should show no
decimal point and no decimals (two zeros) right of the (not showing) decimal
point.
If the number after rounding to two decimals is not integer it should always
show two decimals, even if the rightmost decimal (second decimal) is zero.
All results vertically aligned at the decimal point, wether it shows or not"

Second.

You said:
Your second sub "Private Sub Worksheet_Calculate()" works through a for next
loop - which can take a lot of time for a large spreadsheet with a lot of
formulas, as is the case in my workbook - and the loop is triggered by the
calculate event. Can it be done fore all formula cells in the sheet in the
the same time, perhaps by something like

for each c in my....

or perhaps with something like

SpecialCells(xlCellTypeFormulas)

I would be very grateful if you clarify these last two points (missing zero
and all in one execution).

Jack.
Click to expand...
 
Dave,

This will do. I can of course limit the execution to only those colums for
which this matter is of importance.
Will try out your latest code at a later moment, it is 02.40 now, will get
some sleep.
Again many thanks, I learned a lot.

Jack.
 
Back
Top