I am trying to modify some code so that it will only create a single entry for a given user on a given day on the sheet "Review_Tracker". I'm trying to use For and If And to accomplish this, but it's not working as intended. Does anyone see what the problem might be? I looked at some other forum posts regarding duplicate entries and couldn't figure out how to modify them to accommodate the fact that I have two criteria.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim h2 As Worksheet
Dim u2 As Long
Dim i As Integer
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Columns("L:M")) Is Nothing Then
Cells(Target.Row, "M").Value = Date
Cells(Target.Row, "L").Value = Application.UserName
Set h2 = Sheets("Review_Tracker")
u2 = h2.Range("A" & Rows.Count).End(xlUp).Row + 1
For i = 2 To u2
If h2.Cells(i, 1).Value = Date And h2.Cells(i, 2).Value = Application.UserName Then
Exit Sub
h2.Cells(u2, "A").Value = Date
h2.Cells(u2, "B").Value = Application.UserName
h2.Cells(u2, "C").Value = WorksheetFunction.Index(Sheets("Reviewer_Roles").Range("A2:B1000"), _
WorksheetFunction.Match(Application.UserName, Sheets("Reviewer_Roles").Range("A2:A1000"), 0), 2)
End If
Next i
End If
End Sub
Aucun commentaire:
Enregistrer un commentaire