Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number format in a Macro
I have the following number format code in a macro which gives me what I need
except then the cell value is 0, I need it to indicate a "-" (hyphen) or just remain blank. Selection.NumberFormat = "#,##0.0_);(#,##0.0)" Any help would be appreciated. Thank you, -- Dewayne |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number format in a Macro
DeWayne,
If you add a third parameter, that is for zero values, so add ;"-" to what you have if you want zero to display as a hyphen. Your formatting code would then look like this: Selection.NumberFormat = "#,##0.0_);(#,##0.0); "-"" I think you have an extra paren in your first parameter, but maybe that is intentional? -- future_vba_expert "Dewayne" wrote: I have the following number format code in a macro which gives me what I need except then the cell value is 0, I need it to indicate a "-" (hyphen) or just remain blank. Selection.NumberFormat = "#,##0.0_);(#,##0.0)" Any help would be appreciated. Thank you, -- Dewayne |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number format in a Macro
Thank you for you help. It is greatly appreciated.
I want to end up values such as "1,123.4" for millions, "123.4" for hundreds of 1000's, "12.3" for tens of 1000's, 1.23 for 1000"s, 0.1 for 100's and so on. Based on that, which parameter should I eliminate. Thanks again -- Dewayne "future_vba_expert" wrote: DeWayne, If you add a third parameter, that is for zero values, so add ;"-" to what you have if you want zero to display as a hyphen. Your formatting code would then look like this: Selection.NumberFormat = "#,##0.0_);(#,##0.0); "-"" I think you have an extra paren in your first parameter, but maybe that is intentional? -- future_vba_expert "Dewayne" wrote: I have the following number format code in a macro which gives me what I need except then the cell value is 0, I need it to indicate a "-" (hyphen) or just remain blank. Selection.NumberFormat = "#,##0.0_);(#,##0.0)" Any help would be appreciated. Thank you, -- Dewayne |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number format in a Macro
You would keep the same formatting, but divide all the numbers in the range
by 1000. Formatting can not change the value, you have to divide to get a new number if you want to have 1,234.5 instead of 1,234,500. -- future_vba_expert "Dewayne" wrote: Thank you for you help. It is greatly appreciated. I want to end up values such as "1,123.4" for millions, "123.4" for hundreds of 1000's, "12.3" for tens of 1000's, 1.23 for 1000"s, 0.1 for 100's and so on. Based on that, which parameter should I eliminate. Thanks again -- Dewayne "future_vba_expert" wrote: DeWayne, If you add a third parameter, that is for zero values, so add ;"-" to what you have if you want zero to display as a hyphen. Your formatting code would then look like this: Selection.NumberFormat = "#,##0.0_);(#,##0.0); "-"" I think you have an extra paren in your first parameter, but maybe that is intentional? -- future_vba_expert "Dewayne" wrote: I have the following number format code in a macro which gives me what I need except then the cell value is 0, I need it to indicate a "-" (hyphen) or just remain blank. Selection.NumberFormat = "#,##0.0_);(#,##0.0)" Any help would be appreciated. Thank you, -- Dewayne |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number format in a Macro
Thank you very much for your help. It is appreciated.
-- Dewayne "future_vba_expert" wrote: You would keep the same formatting, but divide all the numbers in the range by 1000. Formatting can not change the value, you have to divide to get a new number if you want to have 1,234.5 instead of 1,234,500. -- future_vba_expert "Dewayne" wrote: Thank you for you help. It is greatly appreciated. I want to end up values such as "1,123.4" for millions, "123.4" for hundreds of 1000's, "12.3" for tens of 1000's, 1.23 for 1000"s, 0.1 for 100's and so on. Based on that, which parameter should I eliminate. Thanks again -- Dewayne "future_vba_expert" wrote: DeWayne, If you add a third parameter, that is for zero values, so add ;"-" to what you have if you want zero to display as a hyphen. Your formatting code would then look like this: Selection.NumberFormat = "#,##0.0_);(#,##0.0); "-"" I think you have an extra paren in your first parameter, but maybe that is intentional? -- future_vba_expert "Dewayne" wrote: I have the following number format code in a macro which gives me what I need except then the cell value is 0, I need it to indicate a "-" (hyphen) or just remain blank. Selection.NumberFormat = "#,##0.0_);(#,##0.0)" Any help would be appreciated. Thank you, -- Dewayne |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number format in a Macro
You are welcome. Please rate my post, if you think it helped you. I'm trying
to get a gold star! (still a very long way to go, though) -- future_vba_expert "Dewayne" wrote: Thank you very much for your help. It is appreciated. -- Dewayne "future_vba_expert" wrote: You would keep the same formatting, but divide all the numbers in the range by 1000. Formatting can not change the value, you have to divide to get a new number if you want to have 1,234.5 instead of 1,234,500. -- future_vba_expert "Dewayne" wrote: Thank you for you help. It is greatly appreciated. I want to end up values such as "1,123.4" for millions, "123.4" for hundreds of 1000's, "12.3" for tens of 1000's, 1.23 for 1000"s, 0.1 for 100's and so on. Based on that, which parameter should I eliminate. Thanks again -- Dewayne "future_vba_expert" wrote: DeWayne, If you add a third parameter, that is for zero values, so add ;"-" to what you have if you want zero to display as a hyphen. Your formatting code would then look like this: Selection.NumberFormat = "#,##0.0_);(#,##0.0); "-"" I think you have an extra paren in your first parameter, but maybe that is intentional? -- future_vba_expert "Dewayne" wrote: I have the following number format code in a macro which gives me what I need except then the cell value is 0, I need it to indicate a "-" (hyphen) or just remain blank. Selection.NumberFormat = "#,##0.0_);(#,##0.0)" Any help would be appreciated. Thank you, -- Dewayne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I add phone number format as a permanent custom 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) |