VBA Match Function - Holy Casting Batman!

[XlsmGames]

Oh silly Match function. How you vex me so!

I am using WorksheetFunction.Match() to identify the location of values in a range. Some of the values in the range are strings. Some of the values are dates. And guess what? Match chokes on the dates. It's clearly a type mistmatch issue. However, I cannot figure out a workaround since values in the range could be strings or dates. Can I cast the entire range using CDate? How would that affect the string values?


A simplified version of my code is:

Dim ColNumInTarget As Double
Dim strTextToMatchAs String
Dim rgTargetHeaderRow As Variant
Dim i as Integer

set rgTargetHeaderRow = ActiveSheet.Range("A1:A8")

strTextToMatch = Worksheet("Sheet3").Cells(1, i).Value

ColNumInTarget = WorksheetFunction.Match(strTextToMatch, rgTargetHeaderRow, 0)


rgTargetHeaderRow has values:[TABLE="width: 525"]
<tbody>[TR]
[TD="class: xl74, width: 64"]Q1 Spend[/TD]
[TD="class: xl75, width: 64"]Q2 Spend[/TD]
[TD="class: xl75, width: 64"]Q3 Spend[/TD]
[TD="class: xl76, width: 64"]Q4 Spend[/TD]
[TD="class: xl77, width: 68"]Oct-12[/TD]
[TD="class: xl78, width: 68"]Nov-12[/TD]
[TD="class: xl78, width: 68"]Dec-12[/TD]
[TD="class: xl78, width: 65"]Jan-1[/TD]
[/TR]
</tbody>[/TABLE]

strTextToMatch pulls values from another sheet. Those values need to be matched with values in the range rgTargetHeaderRow. If strTextToMatch = "Q1 Spend", I can get a match. If strTextToMatch = "10/1/2012", I cannot match Oct-12 and I get a 1004 error -- "Unable to get the Match property of the Worksheet Function class".


Long time lurker, first timer poster. If you (the MrExcel community) will accept some flattery, this really is the best excel board on the interwebs. Great explanations and lots of elegant solutions!

 

[Jerry Sullivan]

Hi XlsmGames and Welcome to the Board,

I enjoyed reading your post. Was your vertical "Header" Range A1:A8 a typo, or an homage to Batman's ability to walk up buildings as if he was walking on a flat studio floor?

Here's some alternative code for you to try...

Sub Vexing()
    Dim varResult As Variant
    Dim varTextToMatch As Variant
    Dim varTargetHeaderRow As Variant

 
    varTargetHeaderRow = ActiveSheet.Range("A1:H1")
    varTextToMatch = Worksheets("Sheet3").Cells(1, 3)

    varResult = Application.Match(varTextToMatch, varTargetHeaderRow, 0)
    If IsError(varResult) Then
        MsgBox "Not found"
    Else
        MsgBox "Column: " & varResult
    End If
    
End Sub

 

[XlsmGames]

Hi Jerry,

Thanks. Yes, absolutely intentional homage.

The revised code us much cleaner (it's great to see how people do their variable naming -- I'm trying to pick up some good habits). It also no longer dies upon hitting a date. However, I'm still not getting the desired output. With the slight modification below, I get a msgbox that says "10/1/2012 is not found".

    If IsError(varResult) Then
        MsgBox (varTextToMarch & " is not found")
    Else
        MsgBox "Column: " & varResult
    End If
    
End Sub

Even though there is no type mismatch (since everything is a variant), for some reason I can't two identical dates to Match. I've made sure they are formatted the same way in both sheet (numbers format is Short Date). Any suggestions? I'm vexed and perplexed.


BTW, Sub Vexing() -- hilarious.

Hi XlsmGames and Welcome to the Board,

I enjoyed reading your post. Was your vertical "Header" Range A1:A8 a typo, or an homage to Batman's ability to walk up buildings as if he was walking on a flat studio floor?

Here's some alternative code for you to try...

Sub Vexing()
    Dim varResult As Variant
    Dim varTextToMatch As Variant
    Dim varTargetHeaderRow As Variant

 
    varTargetHeaderRow = ActiveSheet.Range("A1:H1")
    varTextToMatch = Worksheets("Sheet3").Cells(1, 3)

    varResult = Application.Match(varTextToMatch, varTargetHeaderRow, 0)
    If IsError(varResult) Then
        MsgBox "Not found"
    Else
        MsgBox "Column: " & varResult
    End If
    
End Sub

 

[Jerry Sullivan]

Are the month dates (Oct-12, Nov-12) actual date values formated as "MMM-YY" or are they just text strings?
What value appears in the formula box when the cell with Oct-12 is selected?

There are a lot of opinions on variable naming- probably the best advice in this area is to try to be consistent within your code.

I tend to use a one character prefix to designate the most common data types
vMyVariant
lMyLong
rMyRange

In the code above I was trying to be consistent with the 3-character convention you had used for strTextToMatch

 

[XlsmGames]

The value in the formula box is 10/1/2012. The cell displays as Oct-12 (a custom format). I've also tried using the short date and long date formats, but that did not make any difference. Thank you for your time and help.

 

[Jerry Sullivan]

That's interesting. I'm getting a different result on my mockup using those values and formats.

The code I suggested is loading the range into a Variant array, then doing a match on the array, not the range.
Try inspecting the array to see if that provides an explanation or at least a clue.

To do that, add some code to send each item in the array to the Immediate Window of the VBE.

Sub VnP()
    Dim varResult As Variant
    Dim varTextToMatch As Variant
    Dim varTargetHeaderRow As Variant

 
    varTargetHeaderRow = ActiveSheet.Range("A1:H1")

[COLOR="#000080"]    Dim i As Integer
    For i = LBound(varTargetHeaderRow, 2) To UBound(varTargetHeaderRow, 2)
        Debug.Print varTargetHeaderRow(1, i)
    Next i[/COLOR]

 
    varTextToMatch = Worksheets("Sheet3").Cells(1, 3)
    varResult = Application.Match(varTextToMatch, varTargetHeaderRow, 0)
    If IsError(varResult) Then
        MsgBox (varTextToMatch & " is not found")
    Else
        MsgBox "Column: " & varResult
    End If
End Sub

When I run this code on my mockup I get this displayed in my Immediate Window:
Q1 Spend
Q2 Spend
Q3 Spend
Q4 Spend
10/1/2012
11/1/2012
12/1/2012
1/1/2013

 

[XlsmGames]

You are indeed correct (not that I doubted). My actual code did not match your recommendations as closely as I had intended. In fact, I was incorrectly using the following line:

set varResult = Application.Match(varTextToMatch, varTargetHeaderRow, 0)

Oddly, the innocuous seeming "set" command acted like a rather large pipe wrench in my code. Apparently, there IS a difference between object references and value assignments. I can vaguely conceptualize the difference but can't articulate it to save Robin's life.

Code runs great. Life is dandelions and daisies. Many thanks for your help!