locked
Generate serial number if certain cell contains data. RRS feed

  • Question

  • I used this code to get time in cell C if cell B contain data, 

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cel As Range
        If Not Intersect(Range("b5:b60000"), Target) Is Nothing Then
            Application.EnableEvents = False
            For Each cel In Intersect(Range("b5:b60000"), Target)
                If cel.Value = "" Then
                    cel.Offset(ColumnOffset:=1).ClearContents
                Else
                    cel.Offset(ColumnOffset:=1).Value = Time
                End If
            Next cel
            Application.EnableEvents = True
        End If
    End Sub

    How to modify it if I need Cell A contain a generated serial number starting from number 1 and in cell A5.


    • Edited by Ahmed Morsyy Monday, November 4, 2013 4:12 AM grammar
    Monday, November 4, 2013 4:11 AM

Answers

  • Not sure that I have interpreted your question correctly. I have included code (Between the commented asterisk lines) to insert the next serial number in column A on the same row as the cell in column B that has been changed.

    If not what you want then need more explanation of your requirements.

    Private Sub Worksheet_Change(ByVal Target As Range)
         Dim cel As Range
         Dim lngSerial As Long
         'If Not Intersect(Range("b5:b60000"), Target) Is Nothing Then
         If Not Intersect(Range("b5:b60"), Target) Is Nothing Then
             Application.EnableEvents = False
             'For Each cel In Intersect(Range("b5:b60000"), Target)
             For Each cel In Intersect(Range("b5:b60"), Target)
                 If cel.Value = "" Then
                     cel.Offset(ColumnOffset:=1).ClearContents
                 Else
                     cel.Offset(ColumnOffset:=1).Value = Time

                     '************************************************
                     lngSerial = WorksheetFunction.Max(Columns("A:A"))
                     Cells(Target.Row, "A") = lngSerial + 1
                     '************************************************
                 End If
             Next cel
             Application.EnableEvents = True
         End If
     End Sub


    Regards, OssieMac

    • Marked as answer by Ahmed Morsyy Monday, November 4, 2013 8:16 AM
    Monday, November 4, 2013 4:48 AM

All replies

  • Not sure that I have interpreted your question correctly. I have included code (Between the commented asterisk lines) to insert the next serial number in column A on the same row as the cell in column B that has been changed.

    If not what you want then need more explanation of your requirements.

    Private Sub Worksheet_Change(ByVal Target As Range)
         Dim cel As Range
         Dim lngSerial As Long
         'If Not Intersect(Range("b5:b60000"), Target) Is Nothing Then
         If Not Intersect(Range("b5:b60"), Target) Is Nothing Then
             Application.EnableEvents = False
             'For Each cel In Intersect(Range("b5:b60000"), Target)
             For Each cel In Intersect(Range("b5:b60"), Target)
                 If cel.Value = "" Then
                     cel.Offset(ColumnOffset:=1).ClearContents
                 Else
                     cel.Offset(ColumnOffset:=1).Value = Time

                     '************************************************
                     lngSerial = WorksheetFunction.Max(Columns("A:A"))
                     Cells(Target.Row, "A") = lngSerial + 1
                     '************************************************
                 End If
             Next cel
             Application.EnableEvents = True
         End If
     End Sub


    Regards, OssieMac

    • Marked as answer by Ahmed Morsyy Monday, November 4, 2013 8:16 AM
    Monday, November 4, 2013 4:48 AM
  • Thank you OssieMac, this is exactly what I meant. Can you modify that code so that when cell B has no data the serial stops, as the case with the time in cell C, and recontinue when cell B has data? 
    Monday, November 4, 2013 8:16 AM
  • Hello OssieMac, I think if we can modify the code so it take the number from the previous A cell and add 1 to the neighbor A cell if the B cell contains data, else if no data, the neighbor A cell turns blank, can you?
    Monday, November 4, 2013 8:33 AM
  • I don't understand what you want to do.


    Regards, OssieMac

    Tuesday, November 5, 2013 1:35 AM