MAcro to add new data and check for duplicates

[mike_ate_a_pie]

Hi all,

I am trying to find a macro that will enable data to be enetered to a list. The list will then be sorted smallest to biggest and only unique entries will be allowed to be added.

For example:

<table style="border-collapse: collapse;" width="274" border="0" cellpadding="0" cellspacing="0" height="197"><col style="width: 56pt;" width="74"> <col style="width: 86pt;" width="115"> <tbody><tr style="height: 15.75pt;" height="21"> <td style="height: 15.75pt; width: 56pt;" width="74" height="21">enter code</td> <td style="width: 86pt;" width="115">enter description</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" height="21">103008</td> <td class="xl64" style="border-left: medium none;">ryan</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">Code</td> <td class="xl63">Description</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">102001</td> <td class="xl63">mike</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">102006</td> <td class="xl63">john</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">102009</td> <td class="xl63">sam</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">103001</td> <td class="xl63">pete</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">104002</td> <td class="xl63">gary</td> </tr> </tbody></table>
If i entered 103008 and ryan this would be added to the list below in code order (below pete). However if i tried to add a code already in the list, eg - 102006, john, it would create an error box.

Does anybody know if this is possible or a simple way to do this?

Thanks in advance

Mike

 

[rdw72777]

Do you have any code already done or are you asking for the code from scratch?

 

[mike_ate_a_pie]

I havent written any code as yet. I am only a begineer at VBA, i can record the macro to enter and sort the data into the list but the duplication is causing me problems and this probably needs to be written.

Therfore I think its probably best to start from scratch.

Cheers

 

[GTO]

And where is this 'list' to be?

 

[mike_ate_a_pie]

the list would be a few rows below where you enter the information. thanks

 

[nwc]

You could write a macro that scans the list for a match, or alternatively, you could just do a formula next to the input range that will tell you if there is a match. Then make the "add data" macro exit with an error message if the formula shows the data is already in the list. ie.

Formula:
=IF(ISERROR(MATCH([INPUT CELL],[RANGE TO COMPARE TO],0)),"","Already in list")

Macro:
If Range("[CELL W/ ABOVE FORMULA]") = "Already in List" then
MsgBox "This customer cannot be added because the record already exists",vbOKOnly,"Error"
Exit Sub
Else
'Run your code to insert and sort
End If

One benefit of this method is that it provides instant feedback rather than waiting until the macro is run.

 

[mike_ate_a_pie]

thats ace, i've ended up just using the formula, thanks