Autonumber

Hi,

I'm looking for a piece of code that can add an auto number to a cell when the rest of the row is populated.

It doesn't need to have a pre-fix. I've tried this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myVal As Variant
    If Target.Column <> 2 Then Exit Sub

    If Target.Row = 4 Then
        Target.Offset(0, -1).Value = "0001"
    Else
        myVal = Split(Target.Offset(-1, -1).Value, "-")(1)
        Target.Offset(0, -1).Value = "001-" & Format(Val(myVal + 1), "0000")
    End If
    
End Sub

And it works! But only for one row - it wont take the code down onto the next row.

Can anyone give me any advice?

Thanks

Naomi

Hi,

I'm no expert in VBA, but it appears that simply:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myVal As Variant

If Target.Column <> 2 Then Exit Sub

If Target.Row = 4 Then

Target.Offset(0, -1).Value = "0001"

Else

myVal = Target.Offset(-1, -1) + 1
Target.Offset(0, -1).Value = Format(myVal, "0000")

End If

End Sub

Regards

A couple of thoughts.
I'm not sure that you need a macro, since you want the number in column B whenever there is data "somewhere" in rows 4 and beyond. For instance, you could use a cell formula that checks for data in column A and uses the row number to calculate the serial number in column B:
B4: =IF(A4<>"",ROW()-3,"") and copied down will put 1,2,... whenever you enter data in column A.
If you want a prefix you could format column B using "001-"0000