Check for Duplicates when user enters values in the UserForm

**s2jrchoi** · 12-23-2012, 01:08 AM

Excel Gurus,

I have a working UserForm (please see attached) that adds data to a certain worksheet based on the year (2010, 2011, 2012, 2013) value the user enters. I wanted to see if there was a way using VBA to verify if the user is entering a duplicate entry based on certain fields i.e. document number field + total amount + company name + year) while the user is entering the data in the UserForm or after the user clicks the Add Data Button. So if there is a duplicate, a msgbox appears that tells the user that this is a duplicate entry and that the data won't get saved. It will then direct them back to the userform they just came from. If it isn't a duplicate, then it basically functions the way it ought to.

Please let me know if this is doable using VBA.

Thank you for your help in advance.

J

**HaHoBe** · 12-23-2012, 03:16 AM

Hi, s2jrchoi,

right away I think there are at least two ways of doing so. One is using the Autofilter on each sheet and look at the number of rows being displayed (if there are none the entry may be made after the autofilter has been disabled). The other would use Find/FindNext: search for the first criteria, set a range to it (only if range is not nothing something has been found, else exit loop and write data), compare the next item via range object and offset.

Ciao,
Holger

**Leith Ross** · 12-23-2012, 04:15 AM

Hello s2jrchoi,

Here is the modified macro to do what you want plus a little more. This code has been added to the attached workbook.

Please Login or Register  to view this content.

**s2jrchoi** · 12-23-2012, 12:32 PM

Leith,

This is crazy!!! Thank you so much for all the extras!! Now I just need to figure this thing out..lol..thank you again.

J

**Leith Ross** · 12-23-2012, 03:35 PM

Hello J,

I am glad you posted the workbook. Rather than a just a row with column headers, you added Tables to each worksheet. Providing a standard solution to this problem would not have worked.

Tables have built in properties and methods that make it easy to work with the data. One big problem is VBA does not call them Tables but ListObjects. So, if you look for Tables in VBA you won't find any.

A table can be broken down into 3 basic parts: The header row, the data range, and the insertion row. The Header Row displays the names of each column in the table. The Insertion Row is always the last row in the table and ,as the name suggests, it adds a new row to the table. The Data Range is the all cells between the HeaderRow and Insertion Row.

Rows in the Data Range create a collection known as ListRows and are numbered starting at one. Since this is a collection, it has an Add method and Count method. The Count method returns the number of rows in the Data Range. The Add method is used to add another ListRow to the Data Range. If the insertion position argument is missing then the new row will be added above the Insertion row.

There are many more properties and methods to a ListObject. I have listed the ones that are relevant to the macro. Hopefully, this will help you understand more about the code and what it is doing.

**s2jrchoi** · 12-27-2012, 02:02 PM

Hey Leith,

I just got back from my vacation and am finally able to take a look at this code and what you have generously put together. I am having a hard time understanding why I get an error message when I run the SearchForm. The error I am getting is Run-time error '9'. Can you please help when you get a chance?

Thank you!!

J

**Leith Ross** · 12-27-2012, 03:43 PM

Hello s2jrchoi,

Error 9 is a subscript out of range. Without knowing the line the error occurs on in the code, I can not really answer your question. Did you change anything in either the workbook layout or the code?