| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
.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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
.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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
.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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
.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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
.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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| NumberFormat | Excel Discussion (Misc queries) | |||
| numberformat? | Excel Discussion (Misc queries) | |||
| NumberFormat? | Excel Worksheet Functions | |||
| using cells.numberformat | Excel Worksheet Functions | |||
| Setting NumberFormat within a VBA Module | Excel Discussion (Misc queries) | |||
Hybrid Mode
