VBA check duplicate record

**nods** · 10-08-2010, 07:25 AM

Hi I have this form but i want to ammend it so when the user enters the data and clicks run I want the program to check for duplicate records using a combination of WeekDD (input field) and SiteDD (input field) and return a dialog box advising the user and asking if they want to overwrite the data. If the user clicks OK then the data is overwritten if they click cancel the operaiton is stopped.

Can anyone help with this?

Code so far

Private Sub RunBtn_Click()
    
Sheets("Forecast Input").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Me.WeekDD.Value


Sheets("Overview").Range("H5").Value = Me.WeekDD.Value
Me.WeekDD.Value = "Select Week"

Sheets("Forecast Input").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Me.SiteDD.Value
Sheets("Overview").Range("K5").Value = Me.SiteDD.Value
Me.SiteDD.Value = "Select Site"

Sheets("Forecast Input").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Me.SalesFC.Value
Sheets("Overview").Range("H13").Value = Me.SalesFC.Value
Me.SalesFC.Value = "Enter Sales Forecast"
    
Sheets("Forecast Input").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = Me.LabourFC.Value
Me.LabourFC.Value = "Enter Weekly Hours"

Sheets("Overview").Select
    Range("H3").Select
    
Calculate

Many thanks

**Palmetto** · 10-09-2010, 03:02 PM

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