Declaring VBA Date variable and using VBA Index / Match not working

[JeffGrant]

Hi All,

I am using the Index/Match pair in a VBA module. Which I have done many times before. All good.

However, this is first time that I am using the date as a matching variable, and I keep getting it wrong.

I am getting Error 13, data type mis match.

I have got:
1. The FormDate variable declared as Date
2. The Lookup Cell value, the Lookup date, and the Lookup return range are both coming back as Data Type 1, so I am assuming that is correct
3. The format of both the look up value and the return range are in the format of dd-mm-yyyy

I know this line of code works, because when I change it to return other string variables it works great. So it has to do with the date componant

Any ideas and direction is most appreciated because I cant see what I am missing.

Sub IndexMatchCondition()
Dim FormDate As Date
Dim FormTrack as String
Dim RowNum As Integer

On Error Resume Next

For RowNum = 2 To 523

FormDate = Sheet32.Range("A" & RowNum).Value

If Sheet32.Range("B" & RowNum) = "" Then ' Check if FormTrack is blank
        GoTo Skip
    Else
        Sheet32.Range("Z" & RowNum).Value = Application.WorksheetFunction.Index(Sheet8.Range("AX2:AX5000").Value, Application.WorksheetFunction.Match(FormDate, Sheet8.Range("AQ2:AQ5000").Value, 0))
End If

Skip:
Next RowNum
End Sub

 

[Alex Blakenburg]

or a single match with a date, you really should use CLng or CDbl if your region is non-US.

Hey Rory, I have done quite a bit of testing on Match with Dates and as long as they are really dates and not text, I have not been able to make it fail.
(my region format is dd/mm/yyyy)
I have even tried find "dd/mm/yyyy" look up range "d" and match still works (and vice versa).

I would be interested to see an example of it not working.

 

[RoryA]

Hi Alex,

In A2:A34 put dates from 1/1/22 to 2/2/22

Then run:

Sub foo()
Dim d As Date
d = Range("A15").Value
MsgBox Application.WorksheetFunction.Match(d, Range("A2:A34"), 0)
End Sub

Does that work for you? It errors for me in 2016 (which is what I'd expect) whereas using CLng(d) runs fine.

 

[Alex Blakenburg]

Interesting, if you put Range("A15") straight into the Match it works fine without the CLng.
If I put it in another cell and format the cells totally differently it also works fine.
I will have to have play with the data type variable.

 

[RoryA]

Interesting, if you put Range("A15") straight into the Match it works fine without the CLng.

Yes, and if you use Range("A15").Value it fails. That's why I always explicitly cast it.

 

[JeffGrant]

This problem did not get resolved. However, my workaround is fine. Thanks to Rory and Alex for your assistance.

 

[Peter_SSs]

This problem did not get resolved.

In that case, no post should be marked as the solution. Certainly the marked post (#15) does not contain the solution so I have removed the mark.