Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format column to have different decimal places
On my spread sheet I have a column with different equipment numbers. If the
number starts with 25 it needs to have 4 decimal places (25.4444) if it starts with a 26 it needs to have 3 decimal places (26.333). Not sure how to do this we tried conditional formatting and it does not give decimal as option. We tried to format as general but it would drop the zero off the end. Example 26.330 would be 26.33. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format column to have different decimal places
Sounds like you'll need a helper column, or you'll need a worksheet change
event. Which are you open to? Extra column or macro? ******************* ~Anne Troy www.OfficeArticles.com "Branden" wrote in message ... On my spread sheet I have a column with different equipment numbers. If the number starts with 25 it needs to have 4 decimal places (25.4444) if it starts with a 26 it needs to have 3 decimal places (26.333). Not sure how to do this we tried conditional formatting and it does not give decimal as option. We tried to format as general but it would drop the zero off the end. Example 26.330 would be 26.33. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format column to have different decimal places
Let's use Range(A1:A15). The trick is defining x as an Integer.
(you could also adapt this to a worksheet change event) Dim x As Integer, cel As Range For Each cel In Range("A1:A15") If IsNumeric(cel) = True Then x = cel If x = 25 Then cel.NumberFormat = "#,##0.0000" ElseIf x = 26 Then cel.NumberFormat = "#,##0.000" End If End If Next -- steveB Remove "AYN" from email to respond "Branden" wrote in message ... On my spread sheet I have a column with different equipment numbers. If the number starts with 25 it needs to have 4 decimal places (25.4444) if it starts with a 26 it needs to have 3 decimal places (26.333). Not sure how to do this we tried conditional formatting and it does not give decimal as option. We tried to format as general but it would drop the zero off the end. Example 26.330 would be 26.33. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format column to have different decimal places
Branden,
Here is a Worksheet_Change event version. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo BadTarget If Target.Column = 1 Then ' 1 = column A - adjust for your specific column If IsNumeric(Target) And Target = 25 And Target < 26 Then Target.NumberFormat = "0.0000" Else If Target = 26 And Target < 27 Then Target.NumberFormat = "0.000" End If End If End If BadTarget: On Error GoTo 0 End Sub Roy "STEVE BELL" wrote: Let's use Range(A1:A15). The trick is defining x as an Integer. (you could also adapt this to a worksheet change event) Dim x As Integer, cel As Range For Each cel In Range("A1:A15") If IsNumeric(cel) = True Then x = cel If x = 25 Then cel.NumberFormat = "#,##0.0000" ElseIf x = 26 Then cel.NumberFormat = "#,##0.000" End If End If Next -- steveB Remove "AYN" from email to respond "Branden" wrote in message ... On my spread sheet I have a column with different equipment numbers. If the number starts with 25 it needs to have 4 decimal places (25.4444) if it starts with a 26 it needs to have 3 decimal places (26.333). Not sure how to do this we tried conditional formatting and it does not give decimal as option. We tried to format as general but it would drop the zero off the end. Example 26.330 would be 26.33. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format column to have different decimal places
Hi Steve,
The trick is defining x as an Integer. This results in rounding to the nearest integer. In consequence, all numerical values between (and including) 25.5 and 26 are rounded to 26 an formatted accordingly. Similarly, values between (and including) 26.5 and 27 are rounded to 27 and will not, therefore, be formatted by your procedure. It would be better, IMO, to adopt the appoach suggested by Roy or to use the VBA Int function or, if negative values were possible, the Fix function. --- Regards, Norman "STEVE BELL" wrote in message news:iJXBe.81$N91.25@trnddc08... Let's use Range(A1:A15). The trick is defining x as an Integer. (you could also adapt this to a worksheet change event) Dim x As Integer, cel As Range For Each cel In Range("A1:A15") If IsNumeric(cel) = True Then x = cel If x = 25 Then cel.NumberFormat = "#,##0.0000" ElseIf x = 26 Then cel.NumberFormat = "#,##0.000" End If End If Next -- steveB Remove "AYN" from email to respond "Branden" wrote in message ... On my spread sheet I have a column with different equipment numbers. If the number starts with 25 it needs to have 4 decimal places (25.4444) if it starts with a 26 it needs to have 3 decimal places (26.333). Not sure how to do this we tried conditional formatting and it does not give decimal as option. We tried to format as general but it would drop the zero off the end. Example 26.330 would be 26.33. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format column to have different decimal places
Norman,
Thanks for the correction. (I should have tried it out first) Did find the following to work. Incremented a list from 25 - 27 by 0.1 Dim x As Integer, cel As Range For Each cel In Range("b1:b25") x = WorksheetFunction.RoundDown(cel, 0) cel.Offset(0, 1) = x Next -- steveB Remove "AYN" from email to respond "Norman Jones" wrote in message ... Hi Steve, The trick is defining x as an Integer. This results in rounding to the nearest integer. In consequence, all numerical values between (and including) 25.5 and 26 are rounded to 26 an formatted accordingly. Similarly, values between (and including) 26.5 and 27 are rounded to 27 and will not, therefore, be formatted by your procedure. It would be better, IMO, to adopt the appoach suggested by Roy or to use the VBA Int function or, if negative values were possible, the Fix function. --- Regards, Norman "STEVE BELL" wrote in message news:iJXBe.81$N91.25@trnddc08... Let's use Range(A1:A15). The trick is defining x as an Integer. (you could also adapt this to a worksheet change event) Dim x As Integer, cel As Range For Each cel In Range("A1:A15") If IsNumeric(cel) = True Then x = cel If x = 25 Then cel.NumberFormat = "#,##0.0000" ElseIf x = 26 Then cel.NumberFormat = "#,##0.000" End If End If Next -- steveB Remove "AYN" from email to respond "Branden" wrote in message ... On my spread sheet I have a column with different equipment numbers. If the number starts with 25 it needs to have 4 decimal places (25.4444) if it starts with a 26 it needs to have 3 decimal places (26.333). Not sure how to do this we tried conditional formatting and it does not give decimal as option. We tried to format as general but it would drop the zero off the end. Example 26.330 would be 26.33. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format column to have different decimal places
Thanks for all the help guys got it to work, I really appreciate it.
"Branden" wrote: On my spread sheet I have a column with different equipment numbers. If the number starts with 25 it needs to have 4 decimal places (25.4444) if it starts with a 26 it needs to have 3 decimal places (26.333). Not sure how to do this we tried conditional formatting and it does not give decimal as option. We tried to format as general but it would drop the zero off the end. Example 26.330 would be 26.33. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheet is set up for 2 decimal places but format changes | Excel Discussion (Misc queries) | |||
format issues with decimal places | Excel Discussion (Misc queries) | |||
decimal places in format cell | Excel Discussion (Misc queries) | |||
Formula for: Format Decimal places? | Excel Discussion (Misc queries) | |||
My numbers format as a number with two decimal places. | Excel Discussion (Misc queries) |