CODEWITHSUNDEEP
vbaexcelautofillautonumber
RobertC
RobertC

Reputation: 51

VBA auto number cells that contain text

I am trying to find a solution for the following problem: column A contains names and I would like to keep those names there and use a code that will auto number them. Something like:

enter image description here

I have looked at quite a few examples on StackOverflow but what those codes do is mainly auto number some cells without necesarily keeping the text in the same cell: vba auto increment a number?
Could you please help me?

Here is the code I used but I only managed to insert numbers, not keep the text in the same cell. 

Sub autonumber()


Dim i As Integer
Dim cell As Range, rng As Range

Set rng = Range("A1:A10")

i = 1

For Each cell In rng
    cell.Value = "" & i
    i = i + 1
Next cell

End Sub

Upvotes: 0

Views: 3035

Answers (3)

iamanigeeit
iamanigeeit

Reputation: 834

Change

cell.Value = "" & i

to

cell.Value = i & ". " & cell.Value

Also if you just want to select and auto-number then you don't need rng at all: just use

For Each cell In Selection

Upvotes: 1

iamanigeeit
iamanigeeit

Reputation: 834

Adding on to @Luis Curado's answer, if you don't start on the first row (say start at A4) you can do

=ROW(A4)-ROW(A$4)+1&". "&A4

Upvotes: 1

Luis Curado
Luis Curado

Reputation: 756

you can doit without VBA.

just:

  • in cell A1 u put the formula =row(A1)& ". " &C1 and copy to the others

u can create an "autonumber" propagating row(A1) to the cells of column A

good luck

Upvotes: 2

Related Questions