Weird MATCH bug?

[BlueDNA]

Hi all,

i have a very funny situation with the MATCH function in VBA. Essentially when using the MATCH function, sometimes a match is returned and others - not.

A way to get around this is to copy & paste the query value whereby a little clipboard will appear on the paste cell. At this point the MATCH will then yield a result.

Even though the values MsgBox'd are exactly the same....:dunno

I read somewhere that it has something to do with TextQualifier of the cell?

Can someone explain this to me?

Much appreciated!

David

[matthewspatrick]

David,

Can you post an example file?

[BlueDNA]

David,

Can you post an example file?

Patrick,

Definately.

C13 and D13 have the exact same MATCH query value.
C14 and D14 have the exact same MATCH code.


However, C13 was entered manually, whereas D13 source was copied from A27.

Only D14 has the correct result.

Check it out: 3105

David

[geekgirlau]

No, it's not a bug. You need to be careful about whether you are matching text or numeric values. The list you have here is text (even though they look like numbers) so only another text value will provide a match.

If you know that your list is text, you could change your formula to

=MATCH(TEXT(C13,"0000"),A:A,0)

If your list is numeric, change your formula to

=MATCH(VALUE(C13),A:A,0)

[BlueDNA]

No, it's not a bug. You need to be careful about whether you are matching text or numeric values. The list you have here is text (even though they look like numbers) so only another text value will provide a match.

If you know that your list is text, you could change your formula to

=MATCH(TEXT(C13,"0000"),A:A,0)

If your list is numeric, change your formula to

=MATCH(VALUE(C13),A:A,0)


Cool!

Ive been using activecell.value and been getting those problems.
activecell.text fixed it.

How strange, i thought it would have just compared everything. i guess its like a fliter to save computational time? Otherwise it would have to check every single record regardless of the data type. This could be reasonably long if comparing against 1000 records.

As usual, thanks geekgirlau for the help!

:bow:

[geekgirlau]

By the way, a quick tip if you ever need to convert a whole heap of text values to numeric:

Type the digit "1" in an empty cell
Copy the cell
Select all the cells containing text values
Select Edit | Paste Special | Multiply | OK
Delete the cell containing the "1"