Creating auto generated numbers in excel database

[anshikam]

Hello,

I have an excel database where I have 1 column for a UID no which is pretty much a serial number.
I need a formula where the number would auto generate based on the number in the last row +1.
Currently have the formula set where the value of the first row is set at 1 and second row is a1+1 and so on.
However, when I delete a record or add to the database this goes haywire and i have to fix the formulas.

Any work around this?

 

[Yongle]

You cannot do it by formula and expect it to remain static thereafter

VBA required
eg this adds 1 to the value of the last used row in column A and places the value in the empty cell in the next row

Sub AddOne()
    With ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
        .Offset(1) = .Value + 1
    End With
End Sub

 

[anshikam]

I

You cannot do it by formula and expect it to remain static thereafter

VBA required
eg this adds 1 to the value of the last used row in column A and places the value in the empty cell in the next row

Sub AddOne()
    With ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
        .Offset(1) = .Value + 1
    End With
End Sub

am constantly deleting records mid database. Therefore cant use previous row cause it may not always be accurate.

 

[Yongle]

I am puzzled

I need a formula where the number would auto generate based on the number in the last row +1.

Please EXPLAIN why adding 1 to the value of the last used cell in column does not work when that is exactly what you requested

 

[anshikam]

I am puzzled



Please EXPLAIN why adding 1 to the value of the last used cell in column does not work when that is exactly what you requested

It does work. However I have to constantly redrag the formulas to copy in all cells. Everytime i delete a row or add a row it errors and the last serial no is incorrect. The database is in a table. So when i delete a row its not recognizing that for instance then i have 2 rows with sr no as 9 where as it should be 9 and 10 when a row is added. I run several VBA Scripts on this database and have to assign the value of the particular cell which i do. But its pointless since i have to keep redoing the formula.

 

[Yongle]

Ah - now I understand your problem

I mentioned above that you CANNOT use a formula - all numbers in that column must be made static

1. Select the column, COPY & PasteSpecial VALUES

2. Use VBA (provided above) for future values

You must NOT delete the last row otherwise your numbering will go wrong