Thread: Match Function Type Mismatch

I have the following sub routine:

Option Explicit

Sub ImportWeeklyTotals()

    Const TRACKINGEMPLOYEENAMEHEADER = "EMPNAME"
    
    Dim trackingHeaderColumnIndex As Long
    
    trackingHeaderColumnIndex = Application.Match(TRACKINGEMPLOYEENAMEHEADER, Range("A1").EntireRow, 0)
    
    MsgBox Str(trackingHeaderColumnIndex)

End Sub

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

Try changing:

Range("A1").EntireRow

To:

Range("A1:IV1")

Regards

I can't reproduce that error using

Sub snb
  x = Application.Match("EMPNAME", Rows(1), 0)
End Sub

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!).

Option Explicit

Sub ImportWeeklyTotals()

    Const TRACKINGEMPLOYEENAMEHEADER As String = "EMPNAME"
    
    Dim trackingHeaderColumnIndex As Variant
    
    trackingHeaderColumnIndex = Application.Match(TRACKINGEMPLOYEENAMEHEADER, Sheet1.Range("A1").EntireRow, 0)
    
    MsgBox CStr(trackingHeaderColumnIndex)

End Sub

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:

[helpcontext(0x0002179a)]
HRESULT _stdcall Match(
                [in] VARIANT Arg1, 
                [in] VARIANT Arg2, 
                [in, optional] VARIANT Arg3, 
                [out, retval] double* RHS);

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:

Sub foo()

    Debug.Print Cells(4, 3).Address
    Debug.Print Cells(4).EntireRow.Address
    
End Sub

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:

Error_Val       Error Value
#NULL!          Error 2000
#DIV/0!         Error 2007
#VALUE!         Error 2015
#REF!           Error 2023
#NAME?          Error 2029
#NUM!           Error 2036
#N/A            Error 2042

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

MsgBox CStr(Application.Match(SOURCEEMPLOYEENAMEHEADER, Cells(4,1).EntireRow, 0))

MsgBox CStr(Application.Match(SOURCEEMPLOYEENAMEHEADER, Rows(4), 0))

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.

Originally Posted by officialhopsof

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

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

Msgbox 12

Originally Posted by snb

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

Msgbox 12

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:

Sub foo()
    
    MsgBox Application.Match("Foo", Range("a1:A10"), 0)
    
End Sub

Whereas this piece of code, which explicitly casts to a String does not fail:

Sub foo()
    
    MsgBox CStr(Application.Match("Foo", Range("a1:A10"), 0))
    
End Sub

So, using Brandon's example, it is necessary.

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

Sub ImportData()
  MsgBox IIf(IsError(Application.Match("FOO", Range("A1:A10"), 0)), "Not found", Application.Match("FOO", Range("A1:A10"), 0))
End Sub

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!