Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I have the fixed decinal places function for certain cells
I would like to avoid having to enter decimals points, but just for certian
cells, not the entire sheet. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I have the fixed decinal places function for certain cells
If the cell is formated to 2 decimal places then when you type the number 2
then cell will automatically display 2.00. Once you formated one cell for fixed decimal places you can copy just the format and paste it to other cells. Use Paste Special (in the Edit Menu) and select FORMAT. "dleus" wrote: I would like to avoid having to enter decimals points, but just for certian cells, not the entire sheet. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I have the fixed decinal places function for certain c
Thanx Joel, but what I need is to be able to enter 1234 and have it saved in
the cell as 12.34, or enter 56 and have it saved as .56 "Joel" wrote: If the cell is formated to 2 decimal places then when you type the number 2 then cell will automatically display 2.00. Once you formated one cell for fixed decimal places you can copy just the format and paste it to other cells. Use Paste Special (in the Edit Menu) and select FORMAT. "dleus" wrote: I would like to avoid having to enter decimals points, but just for certian cells, not the entire sheet. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I have the fixed decinal places function for certain cells
On Mon, 28 Apr 2008 02:52:11 -0700, dleus
wrote: I would like to avoid having to enter decimals points, but just for certian cells, not the entire sheet. I believe the only way to do that is with an event-triggered macro. Here's one way that might work -- I don't know if I've checked for all the possibilities, so make sure it works as you expect. To enter this, right-click on the worksheet tab and select View Code. Paste the code below into the module that opens. Change AOI to refer to the cell(s) where you are interested in having a fixed decimal. As written, it refers to Column A. (I coded to divide by 100, but if you need a different value, or different values for different cells, that logic can be incorporated). Let me know if this does what you want. =============================================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim AOI As Range, c As Range Set AOI = Range("A:A") If Not Intersect(Target, AOI) Is Nothing Then For Each c In Intersect(Target, AOI) With c If InStr(.Value, ".") = 0 And _ Len(.Value) 0 And _ .HasFormula = False And _ IsNumeric(.Value) Then .Value = .Value / 100 End If End With Next c End If End Sub ================================= --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I have the fixed decinal places function for certain c
Click on Tools | Options | Edit tab and then check Fixed Decimal (half-
way down) and set it to 2. Now when you enter a number it will be assumed that you want 2 decimal places. Be sure to reverse the setting once you are done (or you could have a macro tied to a button which toggles between the two settings). Hope this helps. Pete On Apr 28, 11:48*am, dleus wrote: Thanx Joel, but what I need is to be able to enter 1234 and have it saved in the cell as 12.34, or enter 56 and have it saved as .56 "Joel" wrote: If the cell is formated to 2 decimal places then when you type the number 2 then cell will automatically display 2.00. Once you formated one cell for fixed decimal places you can copy just the format and paste it to other cells. *Use Paste Special (in the Edit Menu) and select FORMAT. "dleus" wrote: I would like to avoid having to enter decimals points, but just for certian cells, not the entire sheet.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I have the fixed decinal places function for certain c
Ron
Thank You. . . This is exactly what I wanted. I have no idea what all the code you wrote does, but it worked great. Could you tell me how I would set another col on the sheet, say col D, to do the same thing. (I did figure out how to change the # decimal points, but couldn't figure out how to get another col set up) Again, thanx for your help doug "Ron Rosenfeld" wrote: On Mon, 28 Apr 2008 02:52:11 -0700, dleus wrote: I would like to avoid having to enter decimals points, but just for certian cells, not the entire sheet. I believe the only way to do that is with an event-triggered macro. Here's one way that might work -- I don't know if I've checked for all the possibilities, so make sure it works as you expect. To enter this, right-click on the worksheet tab and select View Code. Paste the code below into the module that opens. Change AOI to refer to the cell(s) where you are interested in having a fixed decimal. As written, it refers to Column A. (I coded to divide by 100, but if you need a different value, or different values for different cells, that logic can be incorporated). Let me know if this does what you want. =============================================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim AOI As Range, c As Range Set AOI = Range("A:A") If Not Intersect(Target, AOI) Is Nothing Then For Each c In Intersect(Target, AOI) With c If InStr(.Value, ".") = 0 And _ Len(.Value) 0 And _ .HasFormula = False And _ IsNumeric(.Value) Then .Value = .Value / 100 End If End With Next c End If End Sub ================================= --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I have the fixed decinal places function for certain c
Make alterations to AOI
Set AOI = Range("A:A, D:D") Gord Dibben MS Excel MVP On Mon, 28 Apr 2008 18:44:00 -0700, dleus wrote: Ron Thank You. . . This is exactly what I wanted. I have no idea what all the code you wrote does, but it worked great. Could you tell me how I would set another col on the sheet, say col D, to do the same thing. (I did figure out how to change the # decimal points, but couldn't figure out how to get another col set up) Again, thanx for your help doug "Ron Rosenfeld" wrote: On Mon, 28 Apr 2008 02:52:11 -0700, dleus wrote: I would like to avoid having to enter decimals points, but just for certian cells, not the entire sheet. I believe the only way to do that is with an event-triggered macro. Here's one way that might work -- I don't know if I've checked for all the possibilities, so make sure it works as you expect. To enter this, right-click on the worksheet tab and select View Code. Paste the code below into the module that opens. Change AOI to refer to the cell(s) where you are interested in having a fixed decimal. As written, it refers to Column A. (I coded to divide by 100, but if you need a different value, or different values for different cells, that logic can be incorporated). Let me know if this does what you want. =============================================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim AOI As Range, c As Range Set AOI = Range("A:A") If Not Intersect(Target, AOI) Is Nothing Then For Each c In Intersect(Target, AOI) With c If InStr(.Value, ".") = 0 And _ Len(.Value) 0 And _ .HasFormula = False And _ IsNumeric(.Value) Then .Value = .Value / 100 End If End With Next c End If End Sub ================================= --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I have the fixed decinal places function for certain c
On Mon, 28 Apr 2008 18:44:00 -0700, dleus
wrote: Ron Thank You. . . This is exactly what I wanted. I have no idea what all the code you wrote does, but it worked great. Could you tell me how I would set another col on the sheet, say col D, to do the same thing. (I did figure out how to change the # decimal points, but couldn't figure out how to get another col set up) Again, thanx for your help doug Your welcome. Thanks for the feedback. When you say "set another col on the sheet...", I presume you mean to "add" that column to the column A already set, since I wrote how to change it in my previous post. To have multiple areas set to behave this way, you could use the Union method. So to include columns A and D, you would: Set AOI = Union(Range("A:A"), Range("D:D")) The other code basically ensures that the entry is in your area of interest; you are dealing with a number that does not contain a decimal point, and not a formula or a non-numeric entry. In doing some additional testing, it seems I overlooked some entries that will give an incorrect result (e.g. error values and booleans), so I would change the code to this, which will hopefully cover the various entries that might get made. ==================================== Private Sub Worksheet_Change(ByVal Target As Range) Dim AOI As Range, c As Range Set AOI = Union(Range("A:A"), Range("D:D")) If Not Intersect(Target, AOI) Is Nothing Then Application.EnableEvents = False For Each c In Intersect(Target, AOI) With c If VarType(.Value) = 2 And _ VarType(.Value) <= 6 And _ .HasFormula = False Then If InStr(.Text, ".") = 0 Then .Value = .Value / 100 End If End With Next c End If Application.EnableEvents = True End Sub =========================== --ron |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I have the fixed decinal places function for certain c
I apoligize for not being clear on what I'm trying to do.
What I need is for 1 col to be divided by 100 and another col to be divide by 10. One col is a price ($) and the other col is a quantity that has 1 digit after the decimal. I'm trying to get it so that I don't have to enter the decimal points all the time. Your code is working great for me, I just don't know how to set it up for 2 different cols and 2 different divisors. Thanx for your patience doug |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I have the fixed decinal places function for certain c
On Tue, 29 Apr 2008 04:45:02 -0700, dleus
wrote: I apoligize for not being clear on what I'm trying to do. What I need is for 1 col to be divided by 100 and another col to be divide by 10. One col is a price ($) and the other col is a quantity that has 1 digit after the decimal. I'm trying to get it so that I don't have to enter the decimal points all the time. Your code is working great for me, I just don't know how to set it up for 2 different cols and 2 different divisors. Thanx for your patience doug Something like this for Columns A/100 and D/10 should give you some ideas. =============================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim AOI As Range, c As Range Set AOI = Range("A:A,D:D") If Not Intersect(Target, AOI) Is Nothing Then Application.EnableEvents = False For Each c In Intersect(Target, AOI) With c If VarType(.Value) = 2 And _ VarType(.Value) <= 6 And _ .HasFormula = False Then If .Value = Int(.Value) Then Select Case c.Column Case Is = 1 'Column A .Value = .Value / 100 Case Is = 4 'Column D .Value = .Value / 10 End Select End If End If End With Next c End If Application.EnableEvents = True End Sub =============================== Also, please note that this routine does NOT work the same as the "Fixed Decimal" selection in Excel. In particular, it will divide any integer. So entries in Column A of 12 12. 12.00 will all be converted to 0.12 If you want to enter an integer, you must follow it by two zeros in a "divide by 100" column, and by a single zero in a "divide by 10" column. A1: 1200 -- 12 D1: 120 -- 12 --ron |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I have the fixed decinal places function for certain c
On Tue, 29 Apr 2008 09:57:55 -0400, Ron Rosenfeld
wrote: Also, please note that this routine does NOT work the same as the "Fixed Decimal" selection in Excel. If you prefer to have things work as per the "Fixed Decimal" option, and can avoid having to select multiple cells for data entry in your "adjusted" columns, you could use this event macro instead. If you are NOT in one of the desired columns, the Fixed Decimal option is turned off. It turns on the FixedDecimal option when you jump into a cell in the desired ranges; and sets the number of decimals according to the column. =============================== Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim AOI As Range Set AOI = Range("A:A,D:D") Application.FixedDecimal = False If Not Intersect(Target, AOI) Is Nothing Then If Target.Count < 1 Then MsgBox ("Only select a single cell for data entry") Exit Sub Else Application.FixedDecimal = True Select Case Target.Column Case Is = 1 'Column A Application.FixedDecimalPlaces = 2 Case Is = 4 'Column D Application.FixedDecimalPlaces = 1 End Select End If End If End Sub ========================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation with a fixed amount of decimal places | Excel Worksheet Functions | |||
Data validation with a fixed amount of decimal places | Excel Worksheet Functions | |||
Want Fixed Decimal places in only 1 column | Excel Discussion (Misc queries) | |||
tools|options|Edit tab|fixed decimal places | Excel Discussion (Misc queries) | |||
FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. | Excel Discussion (Misc queries) |