ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   .numberformat Q (https://www.excelbanter.com/excel-worksheet-functions/251348-numberformat-q.html)

Aaron
.numberformat Q
 
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
.numberformat Q
 
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.
.

Aaron
.numberformat Q
 
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.

Rick Rothstein
.numberformat Q
 
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.


Rick Rothstein
.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.



All times are GMT +1. The time now is 10:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com