Excel VBA - Check for Duplicate Records

  • Thread starter Thread starter josnah
  • Start date Start date
J

josnah

HI,

I have two worksheets "PV" & "Register".
PV form is to be completed & will be posted to Register when Use
executes the macro "PostToRegister".

I would like to check if the record being posted is a duplicate entry.

There are two cells named "Beneficiary" & "Amount" in the workshee
"PV" which I want to check against Cols "C" & "G" respectively i
worksheet "Register".

i.e., if both Beneficiary & Amount match any record in the workshee
"Register" it should prompt the user that it is a duplicate entry & as
if the user wants to continue. Otherwise it should post to th
register.

Any help is greatly appreciated! Thanks in advance
 
If I were using a worksheet formula, I could use something like:

=sumproduct(--(register!c1:c9999=beneficiary),--(register!g1:g9999=amount))

If that returns more than 0, then there was a match.

In VBA, I could use that same formula:

Option Explicit
Sub testme()
Dim PVWks As Worksheet
Dim RegWks As Worksheet
Dim LastRow As Long
Dim BeneRng As Range
Dim AmtRng As Range
Dim resp As Long
Dim DuplicateFound As Boolean

Set PVWks = Worksheets("PV")
Set RegWks = Worksheets("reg")

With RegWks
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
Set BeneRng = .Range("c1:c" & LastRow)
Set AmtRng = .Range("G1:g" & LastRow)
End With

resp = vbYes
If Application.Evaluate("sumproduct(--(" _
& BeneRng.Address(external:=True) & "=beneficiary)," _
& "--(" & AmtRng.Address(external:=True) & "=amount))") Then
resp = MsgBox(Prompt:="Duplicate found, Continue?", _
Buttons:=vbYesNo)
End If

If resp = vbYes Then
With RegWks
.Cells(LastRow + 1, "C").Value = PVWks.Range("beneficiary").Value
.Cells(LastRow + 1, "G").Value = PVWks.Range("amount").Value
End With
Else
MsgBox "ok!"
End If

End Sub

I used column C to determine the last used row in the Reg worksheet.
 
I just tried it. It worked perfectly!!!
Thank you so much Dave...
I didn't think I could use a Excel formula as well
 
Back
Top