mercredi 2 janvier 2019

VBA: For with embedded If to prevent duplicate entries

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