Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Number in a Column
I'm trying to create a numbering system in a spread sheet with sequential numbering using decimal points. i.e. column A Header 2.1 2.2 2.3 2.4 2.5 etc. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Number in a Column
Do you mean in A1 type formula and copy that down... ="2." & ROW() OR A1 = 2.1 A2 = 2.2 select A1:A2 and drag which will go upto 2.9 and then 3, 3.1 If this post helps click Yes --------------- Jacob Skaria "Jim S" wrote: I'm trying to create a numbering system in a spread sheet with sequential numbering using decimal points. i.e. column A Header 2.1 2.2 2.3 2.4 2.5 etc. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Number in a Column
Hi Jacob, yes and no, for example: I would like to use Column A and start with a decimal number such as: A1 = 2.1 A2 = 2.2 select A1:A2 and drag which will go upto 2.9 or 2.109 or as far as i would like and also be able to add then 3.0, 3.1, etc. I would like it to be able to auto file as well if that is possible. I can do it with out the decimal as a whole number but when i do the decimal, it increments to the following: 2.1 3.1 4.1 5.1 etc. "Jacob Skaria" wrote: Do you mean in A1 type formula and copy that down... ="2." & ROW() OR A1 = 2.1 A2 = 2.2 select A1:A2 and drag which will go upto 2.9 and then 3, 3.1 If this post helps click Yes --------------- Jacob Skaria "Jim S" wrote: I'm trying to create a numbering system in a spread sheet with sequential numbering using decimal points. i.e. column A Header 2.1 2.2 2.3 2.4 2.5 etc. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Number in a Column
btw, i can get it to do the following:
A1= 2.1 A2= 2.2 A3= 2.3 A4= 2.4 A5= 2.5 A6= 2.6 A7= 2.7 A8= 2.8 A9= 2.9 A10= 3.0 A11= 3.1 I would like to continue with A10= 2.10 A11= 2.11 A12= 2.12 etc, to what ever range i want before changing to A199= 3.0 or higher. "Jim S" wrote: Hi Jacob, yes and no, for example: I would like to use Column A and start with a decimal number such as: A1 = 2.1 A2 = 2.2 select A1:A2 and drag which will go upto 2.9 or 2.109 or as far as i would like and also be able to add then 3.0, 3.1, etc. I would like it to be able to auto file as well if that is possible. I can do it with out the decimal as a whole number but when i do the decimal, it increments to the following: 2.1 3.1 4.1 5.1 etc. "Jacob Skaria" wrote: Do you mean in A1 type formula and copy that down... ="2." & ROW() OR A1 = 2.1 A2 = 2.2 select A1:A2 and drag which will go upto 2.9 and then 3, 3.1 If this post helps click Yes --------------- Jacob Skaria "Jim S" wrote: I'm trying to create a numbering system in a spread sheet with sequential numbering using decimal points. i.e. column A Header 2.1 2.2 2.3 2.4 2.5 etc. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Number in a Column
If a macro solution would be acceptable, here is one that should work nicely for you. Simply put the starting number in a cell, then select from that cell to the last cell you want to put your serial number in, and finally run the macro. Sub FillDownWithDecimals() Dim X As Long, W As String, F As String, S As String S = Selection(1).Text W = Int(S) 'Left(S, InStr(S & ".", ".") - 1) F = Mid(S, InStr(S & ".0", ".") + 1) For X = 0 To Selection.Count - 1 With Selection(1).Offset(X) .NumberFormat = "@" .HorizontalAlignment = xlRight .Value = W & "." & F End With F = CStr(Val(F + 1)) Next End Sub Note: The macro changes the format of the selected cells to text (in order to preserve trailing zeroes) and then right-aligns the text within the cell. -- Rick (MVP - Excel) "Jim S" wrote in message ... btw, i can get it to do the following: A1= 2.1 A2= 2.2 A3= 2.3 A4= 2.4 A5= 2.5 A6= 2.6 A7= 2.7 A8= 2.8 A9= 2.9 A10= 3.0 A11= 3.1 I would like to continue with A10= 2.10 A11= 2.11 A12= 2.12 etc, to what ever range i want before changing to A199= 3.0 or higher. "Jim S" wrote: Hi Jacob, yes and no, for example: I would like to use Column A and start with a decimal number such as: A1 = 2.1 A2 = 2.2 select A1:A2 and drag which will go upto 2.9 or 2.109 or as far as i would like and also be able to add then 3.0, 3.1, etc. I would like it to be able to auto file as well if that is possible. I can do it with out the decimal as a whole number but when i do the decimal, it increments to the following: 2.1 3.1 4.1 5.1 etc. "Jacob Skaria" wrote: Do you mean in A1 type formula and copy that down... ="2." & ROW() OR A1 = 2.1 A2 = 2.2 select A1:A2 and drag which will go upto 2.9 and then 3, 3.1 If this post helps click Yes --------------- Jacob Skaria "Jim S" wrote: I'm trying to create a numbering system in a spread sheet with sequential numbering using decimal points. i.e. column A Header 2.1 2.2 2.3 2.4 2.5 etc. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Number in a Column
Jacob, your formula of ="2." & ROW() works if i'm in row A1 but if i have a header row and want to start in A2= 2.0 or 2.1 what do I need to use? "Jim S" wrote: btw, i can get it to do the following: A1= 2.1 A2= 2.2 A3= 2.3 A4= 2.4 A5= 2.5 A6= 2.6 A7= 2.7 A8= 2.8 A9= 2.9 A10= 3.0 A11= 3.1 I would like to continue with A10= 2.10 A11= 2.11 A12= 2.12 etc, to what ever range i want before changing to A199= 3.0 or higher. "Jim S" wrote: Hi Jacob, yes and no, for example: I would like to use Column A and start with a decimal number such as: A1 = 2.1 A2 = 2.2 select A1:A2 and drag which will go upto 2.9 or 2.109 or as far as i would like and also be able to add then 3.0, 3.1, etc. I would like it to be able to auto file as well if that is possible. I can do it with out the decimal as a whole number but when i do the decimal, it increments to the following: 2.1 3.1 4.1 5.1 etc. "Jacob Skaria" wrote: Do you mean in A1 type formula and copy that down... ="2." & ROW() OR A1 = 2.1 A2 = 2.2 select A1:A2 and drag which will go upto 2.9 and then 3, 3.1 If this post helps click Yes --------------- Jacob Skaria "Jim S" wrote: I'm trying to create a numbering system in a spread sheet with sequential numbering using decimal points. i.e. column A Header 2.1 2.2 2.3 2.4 2.5 etc. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Number in a Column
Here is the macro modified to let you change numbering within the selection... Sub FillDownWithDecimals() Dim X As Long, W As String, F As String, S As String S = Selection(1).Text W = Int(S) 'Left(S, InStr(S & ".", ".") - 1) F = Mid(S, InStr(S & ".0", ".") + 1) For X = 0 To Selection.Count - 1 With Selection(1).Offset(X) If .Text < "" Then S = .Text W = Int(S) 'Left(S, InStr(S & ".", ".") - 1) F = Mid(S, InStr(S & ".0", ".") + 1) End If .NumberFormat = "@" .HorizontalAlignment = xlRight .Value = W & "." & F End With F = CStr(Val(F + 1)) Next End Sub To see what I mean, go to a blank column and in any cell in that column, put 2.0; then, in the same column, put 3.0 in a cell some number of rows below the 2.0 cell; now, make a selection starting with the 2.0 cell and going past the 3.0 cell... run the macro and the 2.# series will stop when it reaches the 3.0 cell and from then on, the number will be 3.# until the end of the selection. You can encompass as many new numbering starting points (the 2.0 and 3.0 values, although you don't have to start with those number) as you want and the code will adapt to them. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... If a macro solution would be acceptable, here is one that should work nicely for you. Simply put the starting number in a cell, then select from that cell to the last cell you want to put your serial number in, and finally run the macro. Sub FillDownWithDecimals() Dim X As Long, W As String, F As String, S As String S = Selection(1).Text W = Int(S) 'Left(S, InStr(S & ".", ".") - 1) F = Mid(S, InStr(S & ".0", ".") + 1) For X = 0 To Selection.Count - 1 With Selection(1).Offset(X) .NumberFormat = "@" .HorizontalAlignment = xlRight .Value = W & "." & F End With F = CStr(Val(F + 1)) Next End Sub Note: The macro changes the format of the selected cells to text (in order to preserve trailing zeroes) and then right-aligns the text within the cell. -- Rick (MVP - Excel) "Jim S" wrote in message ... btw, i can get it to do the following: A1= 2.1 A2= 2.2 A3= 2.3 A4= 2.4 A5= 2.5 A6= 2.6 A7= 2.7 A8= 2.8 A9= 2.9 A10= 3.0 A11= 3.1 I would like to continue with A10= 2.10 A11= 2.11 A12= 2.12 etc, to what ever range i want before changing to A199= 3.0 or higher. "Jim S" wrote: Hi Jacob, yes and no, for example: I would like to use Column A and start with a decimal number such as: A1 = 2.1 A2 = 2.2 select A1:A2 and drag which will go upto 2.9 or 2.109 or as far as i would like and also be able to add then 3.0, 3.1, etc. I would like it to be able to auto file as well if that is possible. I can do it with out the decimal as a whole number but when i do the decimal, it increments to the following: 2.1 3.1 4.1 5.1 etc. "Jacob Skaria" wrote: Do you mean in A1 type formula and copy that down... ="2." & ROW() OR A1 = 2.1 A2 = 2.2 select A1:A2 and drag which will go upto 2.9 and then 3, 3.1 If this post helps click Yes --------------- Jacob Skaria "Jim S" wrote: I'm trying to create a numbering system in a spread sheet with sequential numbering using decimal points. i.e. column A Header 2.1 2.2 2.3 2.4 2.5 etc. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Number in a Column
Hi Jim
In A2; try ="2." & (ROW()-1) If this post helps click Yes --------------- Jacob Skaria "Jim S" wrote: Jacob, your formula of ="2." & ROW() works if i'm in row A1 but if i have a header row and want to start in A2= 2.0 or 2.1 what do I need to use? "Jim S" wrote: btw, i can get it to do the following: A1= 2.1 A2= 2.2 A3= 2.3 A4= 2.4 A5= 2.5 A6= 2.6 A7= 2.7 A8= 2.8 A9= 2.9 A10= 3.0 A11= 3.1 I would like to continue with A10= 2.10 A11= 2.11 A12= 2.12 etc, to what ever range i want before changing to A199= 3.0 or higher. "Jim S" wrote: Hi Jacob, yes and no, for example: I would like to use Column A and start with a decimal number such as: A1 = 2.1 A2 = 2.2 select A1:A2 and drag which will go upto 2.9 or 2.109 or as far as i would like and also be able to add then 3.0, 3.1, etc. I would like it to be able to auto file as well if that is possible. I can do it with out the decimal as a whole number but when i do the decimal, it increments to the following: 2.1 3.1 4.1 5.1 etc. "Jacob Skaria" wrote: Do you mean in A1 type formula and copy that down... ="2." & ROW() OR A1 = 2.1 A2 = 2.2 select A1:A2 and drag which will go upto 2.9 and then 3, 3.1 If this post helps click Yes --------------- Jacob Skaria "Jim S" wrote: I'm trying to create a numbering system in a spread sheet with sequential numbering using decimal points. i.e. column A Header 2.1 2.2 2.3 2.4 2.5 etc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel changes the last number I type in column??? | Excel Discussion (Misc queries) | |||
Excel Number in a Column | Excel Programming | |||
How do I turn excel columns from column number to column letter? | Setting up and Configuration of Excel | |||
how to set up a column in excel so it can auto number | Excel Discussion (Misc queries) | |||
Excel - Return column alphabet from column number | Excel Programming |