Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
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) | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
Number format based on number format of another cell in another workbook | Excel Programming | |||
excel format cells/Number/Category: Number problem | Excel Discussion (Misc queries) |