VBA: If And to check for duplicate previous entries

michaeltsmith93

Board Regular
Joined
Sep 29, 2016
Messages
83
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?

Code:
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
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Some further searching has lead me to this, but now I'm getting an error indicating Sub or Function not defined.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,837
Messages
6,181,255
Members
453,028
Latest member
letswriteafairytale

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top