Posts by stoic_bir

    Re: Generate Auto Number Using VBA


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cell As Range
        If Not Intersect(Range("H:H"), Target) Is Nothing Then
            For Each cell In Intersect(Range("H:H"), Target).Cells
                If cell.Value <> "" And Range("A" & cell.Row).Value = "" Then
                    Range("A" & cell.Row).Value = Application.Max(Range("A:A")) + 1
                End If
            Next
        End If
    End Sub

    Hello All


    I have in my sheet the below formula:
    =IF(ISBLANK(H12),"",A11+1)
    To generate an automatic sequence number, depend on the cell in H column if it is not blank
    I have a problem in above formula; when delete one row the sequence number following that deleted row gives errors


    Whatever


    I think to change it to VBA
    May I have VBA code doing the same job? But without any problem when deleting any row and the autonumber should remain constant once it has been assigned.


    I hope it is clear