VBA WorksheetFuction.Match - Working with pulled Date from MID function

[Lres81715]

Hi all,

I'm having an issue with my VBA code not "finding" the match in the correct column.

When I use the formula =MATCH(MID(Sheet3!A4,13,10)*1,A1:AE1,0)
it gives 2/1/2016 to match the header 2/1/2016, it gets me the value 21 which is Column V on my sheet. This is correct. I'm after the Column number 21 to use in my VBA further along down the line.

The problem is, I'm getting a Run-time error '1004' [Unable to get the Match Property of the WorksheetFuction class] as shown below. or I get Run-time error '13' Type Mismatch if I insert the *1 in Format(Mid(sh3.Range("A4") * 1, 13, 10), "m/d/yyyy")

    Dim sh1         As Worksheet
    Dim sh3         As Worksheet
    Dim rowDt       As String
    Dim ColNum      As String

    Set sh1 = Sheets("Sheet1")
    Set sh3 = Sheets("Sheet3")

    rowDt = Format(Mid(sh3.Range("A4"), 13, 10), "m/d/yyyy") 'Sheet 3 has [B]Start Date: 2/1/2016[/B] which the date is extracted.
    ColNum = Application.WorksheetFunction.Match([B]rowDt[/B], sh1.Range("A1:AZ1"), 0) '

To recreate the problem, Sheet1'! has Headers columns A1 thru AZ that are 6/1/2014, 7/1/2014, 8/1/2014 ... 12/1/2016, 1/1/2017 etc. Sheet3'!A4 has: Start Date: 2/1/2016

[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD="align: center"]Sheet1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]V[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]6/1/2014[/TD]
[TD="align: center"]7/1/2014[/TD]
[TD="align: center"]8/1/2014[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]1/1/2016[/TD]
[TD="align: center"]2/1/2016[/TD]
[TD="align: center"]3/1/2016[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD]Sheet3[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Start Date: 2/1/2016[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks for any assistance.

 

[Norie]

In your formula you are converting the string returned from the MID function to a number by multiplying by 1, in your code you aren't doing that.

So in the code Match is looking for a string not a number.

Try using one of the conversion functions, eg Val, CInt etc, in the code to convert the result from Mid to a number.

 

[AlphaFrog]

Try this...

    Dim sh1         As Worksheet
    Dim sh3         As Worksheet
    Dim rngDate     As Date
    Dim ColNum      As Variant
    
    Set sh1 = Sheets("Sheet1")
    Set sh3 = Sheets("Sheet3")
    
    rowDt = CDate(Mid(sh3.Range("A4"), 13, 10)) 'Sheet 3 has Start Date: 2/1/2016 which the date is extracted.
    ColNum = Application.Match(rowDt, sh1.Range("A1:AZ1"), 0) '
    If Not IsError(ColNum) Then
        'Match found
    Else
        'No match found
    End If

 

[Lres81715]

In your formula you are converting the string returned from the MID function to a number by multiplying by 1, in your code you aren't doing that...

Yes, I mentioned I was getting two types of Error codes.

or I get Run-time error '13' Type Mismatch if I insert the *1 in Format(Mid(sh3.Range("A4") * 1, 13, 10), "m/d/yyyy")

The code doesn't like the *1 so I took it out for the purpose of showing [Unable to get the Match Property of the WorksheetFuction class] error which has been the problem all along.

As for using Val or CInt, I haven't had any luck with your suggestions so far. Thanks though

 

[Lres81715]

Try this...

    rowDt = CDate(Mid(sh3.Range("A4"), 13, 10)) 'Sheet 3 has Start Date: 2/1/2016 which the date is extracted.
    ColNum = Application.Match(rowDt, sh1.Range("A1:AZ1"), 0) '
    If Not IsError(ColNum) Then
        'Match found
    Else
        'No match found
    End If

I tried this and it doesn't work, It still gives me the [Unable to get the Match Property of the WorksheetFuction class] Error 1004.

I have code in place further up that prevents needing If Not IsError(ColNum) Then. If the code has gotten this far, the Match is guaranteed at this point. The problem is (from what I'm guessing) is MID is making the date into a String but the MATCH requires the exact date found in A1:AZ1.

Question for you that might shed some light. The Header cells for 6/1/2014 thru 12/1/2017 are visually formatted to display as "mmm-yy" so Jun-14, Jul-14, Aug-14, Etc. I don't think that would or should make a difference as the contents of the cell show the actual Date.

 

[Norie]

What are you actually trying to look for?

If you are trying to match a value against a set of values you should be looking for the value itself, not how it's formatted for display.

 

[AlphaFrog]

Try this.

    rowDt = [B]CDate[/B](Mid(sh3.Range("A4"), 13, 10)) 'Sheet 3 has Start Date: 2/1/2016 which the date is extracted.
    ColNum = Application.Match([B]CLng[/B](rowDt), sh1.Range("A1:AZ1"), 0) '

The CDate function converts the string from the MID function to a date (rowDT was declared as type Date).
The CLng function converts the date to a long in which the Match function seems to find a serial date match (at least in my test it did).

 

[Lres81715]

What are you actually trying to look for?

If you are trying to match a value against a set of values you should be looking for the value itself, not how it's formatted for display.

Ok, I think I got it work correctly now. I was running into two problems, one of which was from an earlier piece of code that I corrected. CInt you mentioned above was my tip off.

Secondly, I used a DateValue instead which did the trick combined with your .Value suggestion. Triple whammy collision.

Here is the corrected code that works

    Dim sh1         As Worksheet    Dim sh3         As Worksheet
    Dim rowDt       As String
    Dim ColNum      As String


    Set sh1 = Sheets("Sheet1")
    Set sh3 = Sheets("Sheet3")


    rowDt = [B]DateValue[/B](Mid(sh3.Range("A4"), 13, 10))
    ColNum = Application.WorksheetFunction.Match(rowDt, sh1.Range("A1:AZ1")[B].Value[/B], 0) '

Thanks Norie and AlphaFrog! Much appreciated!