VBA to find exact value amongst "Indentical" values

[Foreverlearning]

Hi,

What VBA code can be used to find the exact value amongst other identical values but unique values.

I have attached a simple column of data to explain example.

Thanks

[Paul]

Hi Forever,

Well, to be technical, there's no such thing as identical but unique unless you only have one value and consider it identical to itself. As for your issue of finding a value in a list and displaying its address, you could try:

Please Login or Register  to view this content.

[Firefly2012]

Forever

Your question and indeed what you had written in your attached file implies that you have tried something already - I think it would help to post what you have tried so that we can point out why it might not be working.

[Foreverlearning]

When I use the Find statement it always finds the first part of the value which is the same as others but not completly.

Example:
0234-wrt-rty
0234-wrt-rty-wer
0234-wrt-rty-wee

If I am looking for 0234-wrt-rty-wee it finds 0234-wrt-rty 1st
This is using a textbox as

Please Login or Register  to view this content.

.find(me.txtBox.value)

Please Login or Register  to view this content.

[Firefly2012]

What is the value in Me.TxtBox.Value?

[Foreverlearning]

The Value is 0234-wrt-rty-wer
or when there is a space in between

eg:
0234 wrt rty wer that is if the data had no "-" between them

[Firefly2012]

That should work - any chance you can attach a workbook with the sample data and texbox? Include the textbox value that doesn't work (eg in a spare cell) and indicate how the code was being executed.

[Foreverlearning]

Hi,
I have attached an example

When you select to find 1st 2 examples they are correct but when you select
to find just jam it finds the value before it

How do you find exact value in combo box

Thanks

[mikerickson]

This is an absolutly blind guess, but if you are using the .Find method, the LookAt argument should be set to xlWhole.

[Foreverlearning]

Thanks Mike.

So you are saying that we must use xlwhole to find correct value otherwise it finds 1st value part

[Foreverlearning]

Thanks Mike.

So you are saying that we must use xlwhole to find correct value otherwise it finds 1st value part.

The correct way to add to this code

Please Login or Register  to view this content.

would be? I mean how would you code to find...

Thanks

[Firefly2012]

That would be:

Please Login or Register  to view this content.

Note that what you said in post #4 would not be solved by this - but I suspect you didn't actually have these criteria failing. In your workbook, "jam" is part of all the values, so if you don't specify the match to be on the entire cell value (i.e. xlWhole) and it is rather set to xlPart then the "jam" will match against any part of the cell value (in this case the first 3 characters).

Find's argument settings persist between function calls, so it is always best to be completely explicit every time you use it in code (ie always give a value to the optional arguments). This can make your code longer, but at least you guarantee it should behave how you mean it to.

[Foreverlearning]

Thanks Firefly & Mike

That explains the issue. Learn something all the time