Using Instr to find duplicate strings in a range. Much pain.

[Sc0ut]

Hello everyone,

I'm really struggling with this one. There must be a simple way to get this function to work. I have been trying for hours but I'm not having any joy.

I simply want to stop two of the same names being entered in a range.

Here is what I have so far.

Please Login or Register  to view this content.

I still can't get find a way to get the function to use Instr to find a duplication. The Instr finds the name that I'm using to search the string (the Sname variable), and returns a "true" result (as it has found the match). Obviously this is a problem as I only want the Instr to give a "true" result when it finds more than one of the same name.

Any help would be greatly appreciated.

[Jakobshavn]

Begin by replacing:

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

There may be other problems.

[Sc0ut]

Thanks.

The range is fed though fine, though.

[Jakobshavn]

Here is an alternative way to detect duplicates in a Range:

Please Login or Register  to view this content.

[Sc0ut]

Thank you so much! This looks really good. Could you please help me understand how it works?

[Norie]

Try using CountIf.

This formula will return True if any duplicates are found in Rng1.

Please Login or Register  to view this content.

[Sc0ut]

This looks excellent. Thanks Norie. You guys make it look so easy! I've been toiling for many, many hours.

[Sc0ut]

Hey Norrie, with the "DuplicateCheck = True", I can't use the result of this variable (i.e. True or False) outside of this function. Why's might that be? I'm sorry if it's obvious!

[Norie]

What do you mean exactly?

Where/how are you trying to use DuplicateCheck?

You would use it something like this.

Please Login or Register  to view this content.

[Sc0ut]

When I call the function using this:

Please Login or Register  to view this content.

I'm getting a compile error:

"ByRef argument type mismatch"

On "Rng"

In this line:

Please Login or Register  to view this content.

Why might this be?

[Norie]

How have you declared Rng?

[Sc0ut]

I just had to Dim it first

Please Login or Register  to view this content.

Thank you so much Norri! I can't tell you how helpful this is.

Could you tell me if there is a to clear the cell that now has the duplicate name entered?

[Sc0ut]

This does the trick:

Please Login or Register  to view this content.

Thank you so much! You and the others have been wonderfully helpful.

[Norie]

Where are you using the code?