VBA check duplicate record

It is probably easiest to use the COUNTIF worksheet function, though you could use the Find Method as well.

    Dim ans As Variant
   
    If WorksheetFunction.CountIf(Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row), Me.WeekDD.Value) > 0 Then
        If WorksheetFunction.CountIf(Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row), Me.SiteDD.Value) > 0 Then
            ans = MsgBox("Duplicate - overwrite?", vbYesNo)
        End If
    End If
    Select Case ans
        Case vbYes: 'code to overwite existing value
        Case vbNo: Exit Sub
    End Select