.numberformat Q
I posted the wrong code! Try this code instead...
Dim C As Range, Fraction As String
....
....
....
For Each C In Range("F42,H42,L42,Q42")
If Len(C.Value) = 0 Then
C.NumberFormat = "General"
Else
C.NumberFormat = Trim(Format(String(Len(Int(C.Value)) - 1, _
"#"), "@@@ @@@ @@@ @@@ @@0"))
Fraction = Mid(C.Value, InStr(C.Value & ".", "."))
If Len(Fraction) 1 Then
If C.NumberFormat = "General" Then C.NumberFormat = "0"
C.NumberFormat = C.NumberFormat & Trim(Format(String( _
Len(C.Value) - InStr(C.Value & ".", ".") _
- 1, "#"), "!.0@@ @@@ @@@ @@@ @@@"))
End If
End If
Next
--
Rick (MVP - Excel)
"Rick Rothstein" wrote in message
...
Give this code a try...
Dim C As Range, Fraction As String
....
....
....
For Each C In Range("F42,H42,L42,Q42")
If Len(C.Value) = 0 Then
C.NumberFormat = "General"
Else
C.NumberFormat = Trim(Format(String(Len(Int(C.Value)) - 1, _
"#"), "@@@ @@@ @@@ @@@ @@0"))
Fraction = Mid(C.Value, InStr(C.Value & ".", "."))
If Len(Fraction) 1 Then
C.NumberFormat = C.NumberFormat & Trim(Format(String(Len(Int( _
C.Value)), "#"), "!.0@@ @@@ @@@ @@@ @@@"))
End If
End If
Next
What it does is create an individual NumberFormat for the number in the
cell.
--
Rick (MVP - Excel)
"Aaron" wrote in message
...
On Dec 18, 2:11 pm, Rik_UK wrote:
not sure if this will help..
With Range("f42,h42,l42,q42")
If .NumberFormat = "0" Or .NumberFormat = "General" Then
.NumberFormat = "0.0"
Else
.NumberFormat = "### ##0.0"
End If
End With
--
If this is the answer you hoped for please remember to click the yes
button
below...
Kind regards
Rik
"Aaron" wrote:
Hi,
I have a .numberformat code in place to change 123.12345 to 123.123 45
BUT
I am trying to get it to deal with the numbers before the decimal with
no luck.
With Range("f42,h42,l42,q42")
If .NumberFormat = "0" Or .NumberFormat = "General" Then
.NumberFormat = "0.0"
Else
.NumberFormat = .NumberFormat
.NumberFormat = Replace(.NumberFormat, "00000.0", "00 000.0")
End If
End With
TIA
Aaron.
.
Hi,
No it didnt make any difference.
With Range("f42,h42,l42,q42")
If .NumberFormat = "0" Or .NumberFormat = "General" Then
.NumberFormat = "0.0"
Else
.NumberFormat = .NumberFormat
.NumberFormat = Replace(.NumberFormat, "0000", "000 0")
End If
End With
This code works great for ensuring everything after the DP is in the
structure of 0.000 000 000 000 etc, but I need to do it before the DP
also IE 000 000 000.000 000 000
Any other ideas?
Aaron.
|