+ Reply to Thread
Results 1 to 20 of 20

Match Function Type Mismatch

  1. #1
    Registered User
    Join Date
    06-10-2011
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2003
    Posts
    9

    Match Function Type Mismatch

    I have the following sub routine:

    Please Login or Register  to view this content.
    When I attempt to execute it I get an error stating that there is a type mismatch on the line with the Match function. I would like to note that I have never used VBA or anything of the like before but I am very familiar with Java, C++, Python...etc

    Thank you for your time,
    Brandon
    Last edited by officialhopsof; 06-13-2011 at 04:03 PM. Reason: Solved

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Match Function Type Mismatch

    Try changing:

    Please Login or Register  to view this content.

    To:

    Please Login or Register  to view this content.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Match Function Type Mismatch

    I can't reproduce that error using

    Please Login or Register  to view this content.



  4. #4
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Match Function Type Mismatch

    Hi Brandon,

    The Application.Match() method returns a Double if a match is found or an Error is no match is found. In this case you have declared trackingHeaderColumnIndex as a Long which means that you will get a type mismatch error if a match cannot be found.

    To handle the different scenarios, the usual solution is that which snb posted; declare a Variant type to receive the return value and then check its subtype.

    The other point to address is that you obviously expect it to return a match but, for some reason, it isn't. I expect it's because it is looking at the wrong worksheet. To correct that, qualify the range with its parent worksheet. Finally, to display the retun value in a message box you'll need to use CStr() rather than Str() to cast to a String because using Str() will give a runtime error if trackingHeaderColumnIndex is holding an Error subtype (in fact, I just tried it in XL 2010 and it crashed Excel!).

    Please Login or Register  to view this content.
    Hope that helps,

    Colin

    RAD Excel Blog

  5. #5
    Registered User
    Join Date
    06-10-2011
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Match Function Type Mismatch

    Ah alright, that makes more sense and did fix my issue. Is there a nice central location where I can look up standard functions, their parameters, and their return types? something similar to Javas Javadocs on the language?

    I am running into another match related function problem, I have the following code:

    Please Login or Register  to view this content.


    The first message box pops up with 'true' and the second with '2042'. It may be worth saying that I am using a workbook that I am opening up in code via:

    Please Login or Register  to view this content.


    Thanks again!
    Brandon

  6. #6
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Match Function Type Mismatch

    Hi Brandon,
    Ah alright, that makes more sense and did fix my issue. Is there a nice central location where I can look up standard functions, their parameters, and their return types?
    If you call the worksheet functions via Application, eg Application.Match() then, for VBA management purposes, you can consider them all to have a Variant return type because they can return an Error.

    If you call the worksheet functions via Application.WorksheetFunction, eg Application.WorksheetFunction.Match() then you can check the return type checking the relevant topic in the VBA helpfile. For example, the 'WorksheetFunction.Match method' topic has full details about the syntax, parameters and even says the return type:
    Return Value
    Double
    When you call the worksheet functions via Application.WorksheetFunction they cannot return an Error value (unless specified) so if you pass in arguments which result in an internal error then you will get a runtime error in VBA which will need error handling.



    If you have the OLE/COM Object Viewer then you can see the C++ signatures of the functions, for example the Application.WorksheetFunction.Match() method:
    Please Login or Register  to view this content.


    I am running into another match related function problem, I have the following code.....
    This one is a little easier to spot. If you run the following procedure:
    Please Login or Register  to view this content.
    You will see the following output in the Immediate Window:
    $C$4
    $1:$1
    Cells(4,3) returns a reference to cell D1, not cell A4, so you're checking the wrong row. Error 2042 is the equivalent of a #N/A error value in a worksheet function cell, meaning a match cannot be found. For reference, here are the Error_Val / Value equivalents:

    Please Login or Register  to view this content.
    Last edited by Colin Legg; 06-13-2011 at 03:51 PM. Reason: added list

  7. #7
    Registered User
    Join Date
    06-10-2011
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Match Function Type Mismatch

    Ah alright, I think I follow, so how could I get it to behave as expected?

  8. #8
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Match Function Type Mismatch

    I think you want to check row 4 rather than row 1, so something like one of these:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    By the way, to get the VBA helpfile topic, hopefully it should be just a case of typing in Application.WorksheetFunction.Match into a code module, left clicking on Match so the cursor is flashing on it and then pressing F1.

  9. #9
    Registered User
    Join Date
    06-10-2011
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Match Function Type Mismatch

    Ah fantastic! Thanks!

    I made the mistake of making one nice spreadsheet for some data comparison and now the boss wants me to some fancy excel stuff. Truth is, Ive never touched VBA until friday, so thanks for your patience, I appreciate the help!

    Brandon

    p.s. I can't seem to find the button to mark this thread as solved

  10. #10
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Match Function Type Mismatch

    Quote Originally Posted by officialhopsof View Post
    p.s. I can't seem to find the button to mark this thread as solved
    Glad you got it sorted. See here to mark a thread as solved:

    http://www.excelforum.com/faq.php

  11. #11
    Registered User
    Join Date
    06-10-2011
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Match Function Type Mismatch

    Thanks again!

  12. #12
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Match Function Type Mismatch

    There's no need to use Cstr to show a number in a msgbox.
    Cfr.

    Please Login or Register  to view this content.

  13. #13
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Match Function Type Mismatch

    Quote Originally Posted by snb View Post
    There's no need tot use Cstr to show a number in a msgbox.
    Cfr.
    Please Login or Register  to view this content.
    I disagree with you, snb.

    Firstly, explicitly casting data types is a good coding habit and not one which should be discouraged. Brandon comes from a Java, C++ etc background so this will not be a new concept for him. The MsgBox Prompt parameter expects to be passed a String so explicitly converting a Double (or whatever) with CStr and passing in a String so as not to rely on coercion is better coding, in my opinion.

    Just to back that up with a direct example from this thread, if Application.Match returns an Error value then this piece of code, which relies on coercion, fails with a runtime error:
    Please Login or Register  to view this content.
    Whereas this piece of code, which explicitly casts to a String does not fail:
    Please Login or Register  to view this content.
    So, using Brandon's example, it is necessary.

  14. #14
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Match Function Type Mismatch

    The message 'Error 2042' isn't a message I want a user to be bothered with.
    Id prefer:

    Please Login or Register  to view this content.

  15. #15
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Match Function Type Mismatch

    Brandon is new to VBA and was using it to Debug - it wasn't for a user.

    Personally I wouldn't use your second example either because it makes two calls to Application.Match() even if match is found. Each to their own, I guess!
    Last edited by Colin Legg; 06-13-2011 at 04:50 PM.

  16. #16
    Registered User
    Join Date
    06-10-2011
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Match Function Type Mismatch

    Quote Originally Posted by snb View Post
    Please Login or Register  to view this content.
    I was actually just trying to figure out how to check if no match at all was found, Colin, you mentioned that you wouldn't use the above way due to its multiple calls to Application.Match, is there a more robust solution to this?

  17. #17
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Match Function Type Mismatch

    Quote Originally Posted by officialhopsof View Post
    I was actually just trying to figure out how to check if no match at all was found, Colin, you mentioned that you wouldn't use the above way due to its multiple calls to Application.Match, is there a more robust solution to this?
    Sure, you can do it using the same methodology as snb suggested but using an If...Else...End If block rather than IIf().
    They're both robust, but the former only calls Application.Match() once so that's how I'd do it. But if you want to use IIf() then I'm certainly not going to stop you!

    Please Login or Register  to view this content.
    Last edited by Colin Legg; 06-13-2011 at 04:59 PM.

  18. #18
    Registered User
    Join Date
    06-10-2011
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Match Function Type Mismatch

    AH! I see! Thanks!

  19. #19
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Match Function Type Mismatch

    No difference in robustness involved...

  20. #20
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Match Function Type Mismatch

    Depending on what you want to achieve ( a test or a result):

    Maybe countif is more appropriate, because the result will always be the same vartype:

    Please Login or Register  to view this content.
    If you prefer 'match' you can use it's variable vartype:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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