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.