alternative function to match (need "if containing" function, not an exact match)

[xln00b]

By the help of this wonderful forum I have the following code, which searches a column across all worksheets in a workbook for certain terms, and if matches are found denote such in another column on the same row.

Please Login or Register  to view this content.

what i would want is an alternative to the "match" function, one that would find all the cells 'containing' a search phrase (not only if the cell 'exactly matches' the search phrase)

i know you guys can do it.

[karan]

Use this code instead:

Please Login or Register  to view this content.

The Instr function in VB is the same as the Find function in Excel. It will return the match number. So, if any of the items in the list are present anywhere in the cell, it would return the value "Check" in the second column of the same row.

Let me know if it works for you.

Regards,
Karan

[DonkeyOte]

what i would want is an alternative to the "match" function, one that would find all the cells 'containing' a search phrase (not only if the cell 'exactly matches' the search phrase)

Erm, why not simply use wildcards in the MATCH ? eg:

Please Login or Register  to view this content.

The former being an exact match construct and the latter being a partial match construct.

[karan]

Nice solution DonkeyOte. I tried using '%' but it did not work. So, I worked out this alternate solution.

--Karan--