Excel Number in a Column

[Jim S]

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.

 

[Jacob Skaria]

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

 

[Jim S]

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.

 

[Jim S]

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.

 

[Rick Rothstein]

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.

 

[Jim S]

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?

 

[Rick Rothstein]

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.

 

[Jacob Skaria]

Hi Jim

In A2; try
="2." & (ROW()-1)

If this post helps click Yes