Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number Format
Hi,
Im using ..NumberFormat & " \g" sucessfully, but I cant use .NumberFormat & " \kg" as excel says it cant set the number format. Why should it care about a text inside brackets or is it limited to just one text character? How can I make this work? TIA Aaron. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number Format
It would have been better to post your message back at the original thread
where its content would have been in context. As for your your question, the 'g' seems to have meaning to the NumberFormat, although I'm not sure what it is at the moment (and I'm about to go to sleep for the evening, so I won't look for it until morning here), but the 'k' doesn't. Try using " k\g". Here are your two macros with that change included... Sub DPshiftdown() With Range("Q16") If .NumberFormat = "0 k\g" Or .NumberFormat = "General" Then Exit Sub .NumberFormat = Replace(.NumberFormat, " k\g", "") .NumberFormat = Trim(Left(.NumberFormat, Len(.NumberFormat) - 1)) If Right(.NumberFormat, 1)= "." Then .NumberFormat = Val(.NumberFormat) .NumberFormat = .NumberFormat & " k\g" End With End Sub Sub DPshiftup() With Range("Q16") .NumberFormat = Replace(.NumberFormat, " k\g", "") If .NumberFormat = "0" Or .NumberFormat = "General" Then .NumberFormat = "0.0 k\g" Else .NumberFormat = .NumberFormat & "0 k\g" .NumberFormat = Replace(.NumberFormat, "0000", "000 0") End If End With End Sub -- Rick (MVP - Excel) "Aaron" wrote in message ... Hi, Im using .NumberFormat & " \g" sucessfully, but I cant use .NumberFormat & " \kg" as excel says it cant set the number format. Why should it care about a text inside brackets or is it limited to just one text character? How can I make this work? TIA Aaron. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number Format
On Mon, 8 Dec 2008 22:24:15 -0800 (PST), Aaron
wrote: Hi, Im using .NumberFormat & " \g" sucessfully, but I cant use .NumberFormat & " \kg" as excel says it cant set the number format. Why should it care about a text inside brackets or is it limited to just one text character? How can I make this work? TIA Aaron. Gee, it sure would help if you posted in your original thread. As best I can tell, you are formatting within VBA. And I am also assuming that you want to display something like 123 g As such, your format string is incorrect. For example, if your format string was "General", then the above would resolve to either General \g or General \kg The problem is that you can only follow a slash with a single character. Two solutions: ..NumberFormat & "\k\g" which resolves to General \k\g or ..NumberFormat & """ kg""" which would resolve to General" kg" --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number Format
On Dec 10, 12:37*am, Ron Rosenfeld wrote:
On Mon, 8 Dec 2008 22:24:15 -0800 wrote: Hi, Im using .NumberFormat & " \g" sucessfully, but I cant use .NumberFormat & " \kg" as excel says it cant set the number format. Why should it care about a text inside brackets or is it limited to just one text character? How can I make this work? TIA Aaron. Gee, it sure would help if you posted in your original thread. As best I can tell, you are formatting within VBA. *And I am also assuming that you want to display something like * * * * * 123 g As such, your format string is incorrect. For example, if your format string was "General", then the above would resolve to either * * * * * General \g or * * * * General \kg The problem is that you can only follow a slash with a single character. Two solutions: .NumberFormat & "\k\g" which resolves to * * * General \k\g or .NumberFormat & """ kg""" *which would resolve to * * * * General" kg" --ron Sorry I am new to the groups and thought after a topic was thanked it was sort of closed and not looked at again. I now understand my problem is that you can only follow the \ with one character. The g is for grams and I also would like kg for kilograms and lbf for pounds. I have now made the line " \k\g" for kg and it works great. I assume I can use " \l\b\f" for pounds also but I havent tried that yet. Thanks again everyone. Aaron. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number Format
On Tue, 9 Dec 2008 16:31:51 -0800 (PST), Aaron
wrote: I now understand my problem is that you can only follow the \ with one character. The g is for grams and I also would like kg for kilograms and lbf for pounds. I have now made the line " \k\g" for kg and it works great. I assume I can use " \l\b\f" for pounds also but I havent tried that yet. Thanks again everyone. Aaron. You're welcome. Glad to help. Please not also that the other option is to use "double" the quote marks when you want to include a quote within the quotes eg: """ kg""" or """ lbf""" --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert numbers from text format to number format | Excel Discussion (Misc queries) | |||
Replace million-billion number format to lakhs-crores format | Excel Discussion (Misc queries) | |||
Change number (in text format) to numeric format | Excel Discussion (Misc queries) | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
excel format cells/Number/Category: Number problem | Excel Discussion (Misc queries) |