VBA for Auto insertion of serial numbers

[ElPerson]

Hi

I need a VBA code for auto insertion of the Serial number in column A when entry in Column B

Than you

 

[mumps]

What would your serial numbers look like?

 

[ElPerson]

It's OK just like the following
1
2
3
4

Note that i used manual method and now reached 100
But now i want to automate the process for the future

What would your serial numbers look like?

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter data in the first empty cell in column B and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Target.Offset(0, -1) = Target.Offset(-1, -1).Value + 1
End Sub

 

[ElPerson]

It works well
Thank you

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter data in the first empty cell in column B and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Target.Offset(0, -1) = Target.Offset(-1, -1).Value + 1
End Sub

 

[mumps]

You are very welcome.

 

[Rick Rothstein]

It's OK just like the following
1
2
3
4

Note that i used manual method and now reached 100
But now i want to automate the process for the future

If your data starts on Row (assumes Row 1 has headers), you can do this using a formula also. Put this formula in cell A2 and copy it down to the last row you ever expect to have a serial number in...

=IF(B2="","",MAX(A$1:A1)+1)

An interesting side effect with this formula is that if you skip one or more rows when placing values in Column B, the serial count will continue without any breaks or skips in the numbering.

 

[ElPerson]

the advantage of VBA is that the size of the excel file do not increase a lot and just enter the serial when needed

any way thank you

If your data starts on Row (assumes Row 1 has headers), you can do this using a formula also. Put this formula in cell A2 and copy it down to the last row you ever expect to have a serial number in...

=IF(B2="","",MAX(A$1:A1)+1)

An interesting side effect with this formula is that if you skip one or more rows when placing values in Column B, the serial count will continue without any breaks or skips in the numbering.

 

[Rick Rothstein]

the advantage of VBA is that the size of the excel file do not increase a lot and just enter the serial when needed

Can I assume you will never start with a blank sheet (with or without headers, skip a row when entering data or delete an entry? If you will, you need to test mumps code more carefully.

By the way, 100 or even 1000 formulas is not all that much of a burden to Excel.