+ Reply to Thread
Results 1 to 16 of 16

How do I end routine if duplicate is found?

  1. 03-18-2020, 08:51 AM #1
    Kevchenk0
    Kevchenk0 is offline
    Registered User
    Join Date
    03-05-2020
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2016
    Posts
    59

    How do I end routine if duplicate is found?

    Hello all,

    I have written a macro that adds a row of data to a table when I click a button.

    I would like the macro to alert me with a message if a duplication is found in the 2nd column and then not add anything to the table.

    I have tried a few things with IF statements and message boxes etc but have been unsuccessful

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 03-18-2020, 08:54 AM #2
    Kevchenk0
    Kevchenk0 is offline
    Registered User
    Join Date
    03-05-2020
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2016
    Posts
    59

    Re: How do I end routine if duplicate is found?

    Test file uploaded.

    The button with the code is the generate entry button.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 03-18-2020, 09:21 AM #3
    maras_mak
    maras_mak is offline
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: How do I end routine if duplicate is found?

    Check my suggestion. 
    Please Login or Register  to view this content.
    Best Regards,
    Maras.
    Reply With QuoteRegister To Reply
  4. 03-18-2020, 09:28 AM #4
    Kevchenk0
    Kevchenk0 is offline
    Registered User
    Join Date
    03-05-2020
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2016
    Posts
    59

    Re: How do I end routine if duplicate is found?

    Thanks for this, The only issue I have now is that it adds an empty row if a duplicate is found. How do I stop this from happening?
    Reply With QuoteRegister To Reply
  5. 03-18-2020, 09:36 AM #5
    maras_mak
    maras_mak is offline
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: How do I end routine if duplicate is found?

    Part of the code should look like this. 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 03-18-2020, 09:54 AM #6
    Kevchenk0
    Kevchenk0 is offline
    Registered User
    Join Date
    03-05-2020
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2016
    Posts
    59

    Re: How do I end routine if duplicate is found?

    Hello, It does.. Code is written as you have put but i'm still getting a blank row after I acknowledge the message Attachment 667869
    Reply With QuoteRegister To Reply
  7. 03-18-2020, 10:28 AM #7
    maras_mak
    maras_mak is offline
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: How do I end routine if duplicate is found?

    It is impossible. You did something wrong.
    Here is the whole code for button 7. 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 03-23-2020, 05:27 AM #8
    Kevchenk0
    Kevchenk0 is offline
    Registered User
    Join Date
    03-05-2020
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2016
    Posts
    59

    Re: How do I end routine if duplicate is found?

    Hello, I have found an issue during testing. When the table is empty and I try to add a line to the table it runs an error. If I bypass the error it runs and says its found a duplicate entry. This isn’t an entry in the table though its from the information waiting to go into the table. Any ideas on how to get around this?
    Reply With QuoteRegister To Reply
  9. 03-23-2020, 06:36 AM #9
    maras_mak
    maras_mak is offline
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: How do I end routine if duplicate is found?

    I think it should be good now. 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 03-23-2020, 06:52 AM #10
    Kevchenk0
    Kevchenk0 is offline
    Registered User
    Join Date
    03-05-2020
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2016
    Posts
    59

    Re: How do I end routine if duplicate is found?

    This solves the issue but then creates an issue whereby it adds two entries to the table on generation instead of 1 if it doesn't see a duplicate.
    Reply With QuoteRegister To Reply
  11. 03-23-2020, 06:54 AM #11
    maras_mak
    maras_mak is offline
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: How do I end routine if duplicate is found?

    Add an attachment with this problem.
    Reply With QuoteRegister To Reply
  12. 03-23-2020, 07:00 AM #12
    Kevchenk0
    Kevchenk0 is offline
    Registered User
    Join Date
    03-05-2020
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2016
    Posts
    59

    Re: How do I end routine if duplicate is found?

    Please find file attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 03-23-2020, 07:14 AM #13
    Kevchenk0
    Kevchenk0 is offline
    Registered User
    Join Date
    03-05-2020
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2016
    Posts
    59

    Re: How do I end routine if duplicate is found?

    Sorry works fine, Need to learn to copy all relevant code.
    Reply With QuoteRegister To Reply
  14. 03-23-2020, 07:16 AM #14
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Maybe …


    I can't test now but according to the initial post :

    PHP Code: 
    Private Sub CommandButton7_Click()
            With Sheet16.ListObjects(1)
            If     IsNumeric(Application.Match([B8], .Range.Columns(1), 0)) Then
                MsgBox     "Duplicate", vbExclamation
                Else
               .    ListRows.Add.Range.Value2 = Array([B8], [H8], [N8], [F15], [B15], [R33], [D25], _
                                                      [W27], [P15], [W31], [D29], [D33], [I25], [I29], [I33])
                End If
            End With
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
    Last edited by Marc L; 03-23-2020 at 07:24 AM. Reason: optimization …
    Reply With QuoteRegister To Reply
  15. 03-26-2020, 08:38 AM #15
    Kevchenk0
    Kevchenk0 is offline
    Registered User
    Join Date
    03-05-2020
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2016
    Posts
    59

    Re: How do I end routine if duplicate is found?

    Ok If at the same time I wanted to also send AC16 to CB16 to the table in columns 27 to 79 how would I add this?

    I tried adding:

    Please Login or Register  to view this content.
    But this didn't work
    Reply With QuoteRegister To Reply
  16. 03-26-2020, 11:09 AM #16
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: How do I end routine if duplicate is found?


    Thanks for the rep' !

    As on your attachments the table has less columns so attach a workbook sample according to your new question
    without forgetting to indicate the source sheet of 'AC16:CB16' …
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel 2016 Sort routine issue - recording macro has sheet name in sort routine
    By pongmeister in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-05-2016, 02:45 PM
  2. [SOLVED] naming a routine and calling it in another routine - not a loop
    By HeyInKy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-16-2015, 11:54 AM
  3. Multi-Column Duplicate Finder and If duplicate found, make cell blank in 1st column
    By ImStanding in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-01-2013, 01:27 AM
  4. Pass a variable from one sub-routine to another sub-routine
    By gowtham_pec in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-01-2013, 07:07 PM
  5. [SOLVED] Highlight entire row when duplicate found but leave 1st duplicate blank
    By milo1984 in forum Excel General
    Replies: 4
    Last Post: 09-18-2012, 09:06 AM
  6. Message if duplicate found
    By WhiteKelso in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-26-2007, 11:36 PM
  7. Macros (Sub routine type) not found, Functions are In XLA
    By Andy Kessel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-04-2006, 02:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1