Macro to autonumber cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an excel sheet where I want to be able to select a range of cells in a
column and have excel automatically number them based on the value in a
previous row.

e.g. if A1 = 1, and I select A2 through A4, I want to be able to
automatically number A2 to A4 as 1.1, 1.2, 1.3. The idea is that if I then
insert a new row between A2 and A3, I could select the new A2 through A5 and
renumber as 1.1 through 1.4.

I hope I’ve made it clear what I am after.
 
Jim

one way:

Sub AutoNumberCells()

Dim iCount As Integer
Dim BaseNumber As String
Dim cell As Range

iCount = 1
BaseNumber = Selection(1).Offset(-1, 0)

For Each cell In Selection
cell.Value = BaseNumber & "." & iCount
iCount = iCount + 1
Next 'cell

End Sub

Regards

Trevor
 
The following appears to work for me, however, I also select the row that I
want to use as the number the following rows are based on. In your example,
just select A1 through A4 instead of A2 through A4.

Sub RenumRow()
Dim i As Integer, j As Integer
Dim cell As Range

j = 0

For Each cell In Selection
If Selection.Range("a1").Row = cell.Row Then
i = cell.Value
Else
j = j + 1
cell.Value = i & "." & j
End If
Next cell

End Sub
 
Back
Top