ExcelBanter - Excel VBA - Check for Duplicate Records

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.

"josnah <" wrote:

HI,

I have two worksheets "PV" & "Register".
PV form is to be completed & will be posted to Register when User
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 worksheet
"PV" which I want to check against Cols "C" & "G" respectively in
worksheet "Register".

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

Any help is greatly appreciated! Thanks in advance!

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson