Help on Application.worksheet.function.match / evaluate+match

Hello,

it would be good if i can get assistance for both the methods.

1. The below function works but it gives an (vba) error message if a match is not found. can this be avoided (without using On error)

Please Login or Register  to view this content.

2. what would be equivalent code for the above using evaluate+match combination ( Note: i would like to retain the search criteria - Range("a1:z1") - as it is and do not want to define it as a range / set as a range.

Thanks

If you use Application.Match, rather than Application.WorksheetFunction.Match you can trap errors.

Better still, if you assign the result to a variant variable type then you can test that to see if it's an error ...

Please Login or Register  to view this content.

For part 2 - what are you trying to evaluate?

Thanks for quick responses.

Hi Andrew,

for Part 1: The code given by you works perfectly. ( and hence unable to understand !). the error message what i get is "1004:unable to get the match property of the worksheetfunction class". i was assuming ( erroneously i guess) that this error is triggered only when a match is not found. ( in my code whenever there is a match found it does not give me an error message!!). can you help me understand under what circumstances this error message will be triggered if my assumption is incorrect

for part 2: instead of using application.worksheetfunction.match i would want to use match function using evaluate..for instance something like this
" Evaluate("=match(......) "

I also get a 1004 error when I try to use worksheetfunction.match to look for something that doesn't exist.

Not so sure about the evaluate part - why are you trying to run the match inside an evaluate? There may be a more straightforward way to do the same thing.

If it helps, this code works fine for me - note the double quotes around the search criteria and the range being in worksheet format, not inside a Range() statement ...

Please Login or Register  to view this content.

Thanks Andrew. I was exactly looking for the exact syntax of evaluate+match ! ( the reason why i am trying to use this function is infact to escape from the error which will be generated if i use application.worksheetfunction.match if value is not found! this evaluate+match combination will not give me an error message)