+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Forum Contributor
    Join Date
    02-22-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    102

    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

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Homeless. I am not kidding.
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,135

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

    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

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

    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?

  4. #4
    Forum Contributor
    Join Date
    02-22-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    102

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

    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(......) "

  5. #5
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Help on Application.worksheet.function.match / 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.

  6. #6
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

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

    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.

  7. #7
    Forum Contributor
    Join Date
    02-22-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    102

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

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1