.numberformat Q

[Aaron]

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.

 

[Rik_UK]

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]

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

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.

 

[Rick Rothstein]

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)

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

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.

 

[Rick Rothstein]

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