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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 10:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com