number format?
Morning all.
I'm going through multiple worksheets, and trying to make sure that the values that are in the cells are what's actually showing. I.e., fo rthe most part I set my numeric format to show two decimal places. If however there are 3 decimals in the number, I need to show that. Or, if there are 4 decimal places, I'll need to show them. It's getting rather tedious going through each and every worksheet and doing this one row at a time, manually. As such, I'd like to do this with a macro. So, my question is: What code would I use to check to see the amount of actual decimal places there are in a given number, and then set the number format to show the decimal places that actually exist, with never less than 2 decimal places? At this point, what's rolling through my mind is: If activecell.value = 3 decimal place number then set activecell.format = format("0.000") else if activecell.value = 4 decimal place number then set activecell.format = format("0.0000) else activecell.value = 2 decimal place number set activecell.format = (format("0.00") How would I perform this task? |
number format?
You can't because some number like 1/3 will repeat and not stop at 2 or 3
decimal places. "Steve" wrote: Morning all. I'm going through multiple worksheets, and trying to make sure that the values that are in the cells are what's actually showing. I.e., fo rthe most part I set my numeric format to show two decimal places. If however there are 3 decimals in the number, I need to show that. Or, if there are 4 decimal places, I'll need to show them. It's getting rather tedious going through each and every worksheet and doing this one row at a time, manually. As such, I'd like to do this with a macro. So, my question is: What code would I use to check to see the amount of actual decimal places there are in a given number, and then set the number format to show the decimal places that actually exist, with never less than 2 decimal places? At this point, what's rolling through my mind is: If activecell.value = 3 decimal place number then set activecell.format = format("0.000") else if activecell.value = 4 decimal place number then set activecell.format = format("0.0000) else activecell.value = 2 decimal place number set activecell.format = (format("0.00") How would I perform this task? |
number format?
Ok,
However, in my case, I won't be using fractional numbers for this-- at all. Nor would I use e^, Pi, etc..... All I'll be using will be 0 to 4 decimal place numbers. So, what can I use for this? Thank you for your quick response. "joel" wrote: You can't because some number like 1/3 will repeat and not stop at 2 or 3 decimal places. "Steve" wrote: Morning all. I'm going through multiple worksheets, and trying to make sure that the values that are in the cells are what's actually showing. I.e., fo rthe most part I set my numeric format to show two decimal places. If however there are 3 decimals in the number, I need to show that. Or, if there are 4 decimal places, I'll need to show them. It's getting rather tedious going through each and every worksheet and doing this one row at a time, manually. As such, I'd like to do this with a macro. So, my question is: What code would I use to check to see the amount of actual decimal places there are in a given number, and then set the number format to show the decimal places that actually exist, with never less than 2 decimal places? At this point, what's rolling through my mind is: If activecell.value = 3 decimal place number then set activecell.format = format("0.000") else if activecell.value = 4 decimal place number then set activecell.format = format("0.0000) else activecell.value = 2 decimal place number set activecell.format = (format("0.00") How would I perform this task? |
number format?
Will this work?
For Each cell In UsedRange If IsNumeric(cell) Then Numplaces = Len(cell.Value) - InStr(cell.Text, ".") cell.NumberFormat = "0." & String(Numplaces, "0") End If Next cell "Steve" wrote: Ok, However, in my case, I won't be using fractional numbers for this-- at all. Nor would I use e^, Pi, etc..... All I'll be using will be 0 to 4 decimal place numbers. So, what can I use for this? Thank you for your quick response. "joel" wrote: You can't because some number like 1/3 will repeat and not stop at 2 or 3 decimal places. "Steve" wrote: Morning all. I'm going through multiple worksheets, and trying to make sure that the values that are in the cells are what's actually showing. I.e., fo rthe most part I set my numeric format to show two decimal places. If however there are 3 decimals in the number, I need to show that. Or, if there are 4 decimal places, I'll need to show them. It's getting rather tedious going through each and every worksheet and doing this one row at a time, manually. As such, I'd like to do this with a macro. So, my question is: What code would I use to check to see the amount of actual decimal places there are in a given number, and then set the number format to show the decimal places that actually exist, with never less than 2 decimal places? At this point, what's rolling through my mind is: If activecell.value = 3 decimal place number then set activecell.format = format("0.000") else if activecell.value = 4 decimal place number then set activecell.format = format("0.0000) else activecell.value = 2 decimal place number set activecell.format = (format("0.00") How would I perform this task? |
All times are GMT +1. The time now is 05:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com