Results 1 to 3 of 3

Thread: How To Prevent Adding Duplicated Record In A Table Using VBA

  1. #1
    Junior Member
    Join Date
    Apr 2013
    Posts
    4
    Rep Power
    0

    How To Prevent Adding Duplicated Record In A Table Using VBA

    I need to prevent adding duplicated record in a table .. I attach sample file
    I input date on the left.. when I click a button, the range of records are copied and added to the table .. I need to check if the record has been added before adding it .. The duplicated record is a combination of name and date .. the same account should not be entered twice in the same day .. I appreciate your help .. thanks
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Use this function to identify the duplicate.

    Code:
    Function ISDUPLICATE(ByVal ExeDate As Date, AccountName As String) As Boolean
    
        Dim k   As Variant, i As Long, d As Long
        
        AccountName = LCase(AccountName)
        d = CLng(ExeDate)
        With Worksheets("EXEC Plan")
            k = .Range("s6:t" & .Range("s" & .Rows.Count).End(3).row).Value2
            For i = 1 To UBound(k, 1)
                If k(i, 1) = d And LCase(k(i, 2)) = AccountName Then
                    ISDUPLICATE = True
                    Exit Function
                End If
            Next
        End With
        
    End Function
    and in your sub use it like

    Code:
    If ISDUPLICATE(CDate(Range("b6").Value), Range("c6")) Then Exit Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Apr 2013
    Posts
    4
    Rep Power
    0
    Thanks ..
    This worked perfect !! .. I appreciate your help thank you very much !!

Similar Threads

  1. Export data from Excel to Access Table (ADO) using VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 4
    Last Post: 02-24-2015, 07:53 PM
  2. Excluding Records of one Table from the Other Table
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  3. Copy Table Range Till Last Row Using VBA
    By ivandgreat in forum Excel Help
    Replies: 2
    Last Post: 05-09-2013, 05:41 PM
  4. Vlookup Multiple Values By Adding Formula With Loop In VBA
    By Safal Shrestha in forum Excel Help
    Replies: 15
    Last Post: 04-22-2013, 04:49 PM
  5. Adding Scroll bar to image box in vba
    By princ_wns in forum Excel Help
    Replies: 1
    Last Post: 12-13-2011, 09:47 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •