Finding a Duplicate Value In Excel 3

[CurtR]

I am trying to set up a loop to check a column of numbers in Excel, to ensure that a number being added to this column does not already exsist.
I am having trouble getting the loop to break off when it evaluates, there is no chance for more than one duplication of a number in this column. and the number of rows can vari.

A user will add a number through a form. If the number is already in a column on another sheet then I want to prompt the user to select another number, otherwise have the rest of the macros continue.
Here is what I have

Dim test As Integer
Dim checked As Integer
x = Worksheets("Form&quot.Range("new&quot
c = ActiveCell
For Each c In Worksheets("IMPORT&quot.Range("veh1&quot
If x Like c Then
test = MsgBox(".....! Please Select a Different Number ", vbCritical + vbOKCancel, "Vehicle Number&quot
Select Case test
Case vbOK: Range("new&quot.Select
Case vbCancel: End Select
ElseIf Not x Like c Then
checked = MsgBox("...?", vbCritical + vbYesNo, "Vehicle Information&quot
Select Case checked
Case vbYes: movedata
Case vbNo: End Select

End If
Next
End Sub

Thanks Curt

 

[DaleWatson123321]

Hi Curt,

There are no doubt different solutions to this, but I respectfully suggest that the EASIEST way and FASTEST way, is to utilize Excel's DCOUNTA function.

Here's an example of the formula:
=DCOUNTA(data,1,crit)

...where "data" is a range name assigned to your separate list.

The user input will go into the a cell named "user_input" that is reference by a formula (=user_input) in your criteria range named "crit". The criteria would consist of two cells: The top cell would contain the field name (of the field containing the numbers), and the cell below would contain "=user_input".

Your code would be like the following...

If Range("frm&quot.Value = 1 Then ...

...where "frm" is a range name assigned to the cell containing the DCOUNT formula in a separate worksheet, along with the criteria.

In addition to avoiding the problem you described, avoiding the use of "looping" can also save considerable time.

I hope this helps. Please advise as to how you make out, and whether you need additional help. I can send an example file if you like.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[cheerio]

You could also use the Excel MATCH function to find which row in the existing data has the number you are searching for.

One possible advantage of MATCH is that it will stop searching as soon as a match is found. DCOUNTA will always count through the whole range. So in theory MATCH could be faster. In practice, if duplicates are very rare then most of the time whatever process you use will have to look at every value to prove that no duplicate exists.

I agree with Dale that you should avoid looping in code when an Excel function can do the job as the native functions will always be much faster.

 

[xlbo]

Personally, I find the Find function very useful

x = Worksheets("Form&quot.Range("new&quot
Set foundcell = Sheets("IMPORT&quot.Range("veh1&quot.Find(x)
if Foundcell is nothing then
---continue on
else
msgbox "Number already exists"
end if

HTH
Geoff

 

[GlennUK]

Why don't you set Data Validation on the sheet to perform this functionality? Is there a compelling reason to do this in VBA? If not, look at this link which shows how to vailidate for unique values in a column ....

http://www.j-walk.com/ss/excel/usertips/tip027.htm

Glenn.

 

[cheerio]

There is a sting in the tail for data validation which your link quite fairly discloses at the end. This is that users can very easily get around it so it is not reliable for serious applications.

Apart from the use of Paste/Copy disclosed as a weakness by the link you can also bypass the validation by entering a formula refering to another cell. This is because validation applies to the cell where data is entered not the cell where the result is picked up.

I use data validation for things I do myself because I know the limitations and will not cheat on myself. I do not rely on it for worksheets being completed by others.

Ken

 

[CurtR]

Hi Dale, and Everyone !
Wow Thanks for all of the help!
Y'all have to know that I went and bought some reading material for Visual Basic and VBA, hoping to learn this as it seems that I am required to use it more and more.
I will try these ideas and report back..
Dale, the aplication that you did is working great, and this will be part of that application as we need to be able to add / delete vehicles from the list.
Thanks Again Everyone!!!!

Curt

 

[CurtR]

Hope you guys don't mind VBA 101...
I have tried some of the above.
With the list of Numbers in
Worksheets("VEHICLES&quot.Range("veh&quot
and the New vehicle Number being requested in
WorkSheets("Form&quot.Range(new&quot

I tried this which found no matches even though I know that there are matched in the list.

Sub CommandButton3_Click()
newVeh = Application.WorksheetFunction.Match(Worksheets("Form&quot.Range("new&quot, Worksheets("VEHICLES&quot.Range("veh&quot, 0)
MsgBox newVeh
End Sub

Also I have tired the Find Function again today and still get the error that I was getting when I tried it earlier.
Unable to get the Find Property of the Range Class.

x = WorkSheets("Form&quot.Range("new&quot
Set Foundcell = Sheets("VEHICLES&quot.Range("veh&quot.Find(x)
NOTE: Also Tried WorkSheets("VEHICLES&quot.Range("veh&quot.Find(x)
If Foundcell is nothing Then
movedata ( continue)
else msgbox " Number already exsists"
End If

I am now on to trying the other suggestions that were made, just wanted to see if there was something that I was doing to keep these from working ?
This is both frustrating and interesting.. Though I enjoy Learning
Thanks for your help..... Curt

 

[xlbo]

Apologies - it needs the associated text value

x = WorkSheets("Form&quot.Range("new&quot.TEXT
Set Foundcell = Sheets("VEHICLES&quot.Range("veh&quot.Find(x)

If Foundcell is nothing Then
Movedata
else msgbox " Number already exsists"
End If

Even without that tho, I don't get the Unable to get the Find Property of the Range Class error

I've tested this and it works. I would suggest that if you keep getting that error, you might want to check to see what your range names refer to.
HTh
Geoff

 

[CurtR]

Geoff,

I am still getting the same error.
I created another workbook with just a small list of numbers on one sheet and a single "find Me" number on another sheet.
I have the code exactly as above. and all names ( sheets/ranges) are correct.
I am really curious now as to why this is not working.
if you would email me I would send you this small workbook to examine.
Curt
croberts@mc.rochester.lib.ny.us

 

[xlbo]

Problem solved. When using a Controls Toolbox Commandbutton, you have to explicitly refer to the sheet iehave Sheets("Forms&quot.select (or activate) as the 1st line of the sub because it's a "PRIVATE" sub as opposed to a public sub.
solution is to either use the Sheets...select line or to put the code in a public sub and assign to a FORMS toolbox button
HTH
Geoff

 

[cheerio]

I think we are all learning from this.

Perhaps I should have mentioned that MATCH returns an error value if it cannot return a valid index to a match.

What you can do is put the result of the match into the ISERROR function.

The logic becomes:

if ISERROR(MATCH(...)) then
' no duplicate was found so we can process
else
' we have a duplicate and need a message
end if

Ken

 

[CurtR]

I found this out while toying with Match.
It worked fine so long as there was a match but errored out if there was not. I will go back and see if I can make it work as you have described.
Also the application that started this entire thing is (with this group effort) working to the point that it works ! )


Now, as if I am not already in over my head I am trying to add a Userform instead of having the user input on the spread sheet. Which I have all layed out, Now I just need to figure out how it works !! HA!

Thanks
Curt

 

[cheerio]

good luck

Ken