Generate UniqueId when new/multiple rows are added

[Vbanoob98]

Hi all,

I found this code from @Rick Rothstein

Private Sub Worksheet_Change(ByVal Target As Range)
  Const CourseColumn As String = "b"
  Const UniqueIDcolumn As String = "AF"
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Columns(CourseColumn)) Is Nothing Then
    If IsEmpty(Cells(Target.Row, UniqueIDcolumn).Value) Then
      Cells(Target.Row, UniqueIDcolumn).Value = Format(Now, "'mmddyyyy") & Format(100 * Timer, "0000000")
    End If
  End If
End Sub

It works perfectly except it doesnt work when you copy/paste multiple items/rows. Is there a way to do that?

Thanks !

 

[Vbanoob98]

Ok so I figured it out but I now ran into another problem. How to have 2 different interesect checks?

Option Explicit

Private Sub Worksheet_Change _
            ( _
                       ByVal Target As Range _
            )

  Const s_CheckColumn As String = "Q:Q" 'Column that will be changing
  Const s_CountColumn As String = "AE:AE" 'Column Counter
  Const s_CountDate As String = "Af:Af" 'date added
    
    
  If Intersect(Target, Range(s_CheckColumn)) Is Nothing Then
   If Intersect(Target, Range(ID_CheckColumn)) Is Nothing Then Exit Sub
  Dim rngCell As Range
  For Each rngCell In Intersect(Target, Range(s_CheckColumn))
    With Range(s_CountColumn).Cells(rngCell.Row)
      .Value2 = IIf(.Value2 <> vbNullString, .Value2 + 1, IIf(rngCell.Value2 <> vbNullString, 0, vbNullString))
   
    End With
    Next rngCell

Const ID_CheckColumn As String = "b:b"
Const ID_CountColumn As String = "Ag:Ag" 'Column Counter

If Intersect(Target, Range(ID_CheckColumn)) Is Nothing Then Exit Sub
    
Dim rngCellID As Range
  For Each rngCellID In Intersect(Target, Range(ID_CheckColumn))
    With Range(ID_CountColumn).Cells(rngCellID.Row)
      .Value = Format(Now, "'mmddyyyy") & Format(100 * Timer, "0000000")
    End With
    Next rngCellID

    
End Sub

Any ideas?

 

[Vbanoob98]

Very simple... Instead of the first Exit Sub just Goto CheckID: "then write the next code".