#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 59
Default .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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22
Default .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.
.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 59
Default .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.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default .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.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default .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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
NumberFormat danpt Excel Discussion (Misc queries) 3 May 20th 09 10:55 PM
numberformat? Jack Sons Excel Discussion (Misc queries) 3 September 4th 07 03:44 PM
NumberFormat? alex Excel Worksheet Functions 3 March 1st 07 10:12 PM
using cells.numberformat maxzsim Excel Worksheet Functions 2 May 6th 05 10:41 AM
Setting NumberFormat within a VBA Module Dominic Olivastro Excel Discussion (Misc queries) 3 April 20th 05 12:10 AM


All times are GMT +1. The time now is 04:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"