VBA: If And to check for duplicate previous entries

[michaeltsmith93]

Hi,

I have the following code to catalog worksheet changes. I've added a crude shell of an If And statement to have it check to see if there is a previous item in the table in which the same User has made an edit on the current date. The goal is that only one entry is made for an individual user on a given day. I know that what I have there won't work. Does the solution involve For Each and Next?

Additionally, you'll notice that I'm using Range("B2:B" & u2) to indicate that I'd like the range of all of the cells in Column B beginning at row 2 and ending at the empty row. If I wanted to make this end at the row above the empty row, where would I add the -1? I assume I have to use Row() as well. Is it just Row(u2)-1?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim h2 As Worksheet
    Dim u2 As Long
    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
        [COLOR=#ff0000]If h2.Range("A2:A" & u2) = Date And h2.Range("B2:B" & u2) = Application.UserName[/COLOR] 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
    End If
End Sub

 

[michaeltsmith93]

Some further searching has lead me to this, but now I'm getting an error indicating Sub or Function not defined.

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 Row(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