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

 

[JeffGrant]

Update....

I have since read that changing the Application.WorksheetFunction.Match to Application.Match is more stable in VBA. So I have done this and am now at least getting a #N/A error on the correct rows. So that is at least one step closer

PS. There is no Time component to be concerned with.

 

[JeffGrant]

Update....
By converting all dates to text, I have been able to complete the VBA Index/Match. However, running loops over thousands of dates is adding considerable time to the computations.

This is an OK short term workaround to get me out of trouble today, but wont be desirable do this everyday.

 

[Alex Blakenburg]

Can you remove the ".Value" from both ranges in your index match and see if that fixes it.

Application.Index(Sheet8.Range("AX2:AX5000").Value, Application.WorksheetFunction.Match(FormDate, Sheet8.Range("AQ2:AQ5000").Value, 0))

 

[JeffGrant]

Thanks Alex, Thanks for that. I did try that earlier but without success I'm afraid.

I did have a bit more success with this type of test syntax:

Sub IndexMAtchVBA()
Dim irow As Integer
irow = 0

With Sheet1

For irow = 2 To 5
.Range("g" & irow).Value = Application.Index(.Range("E2:E3"), [COLOR=rgb(226, 80, 65)][B]Application.Match(.Range("A" & irow), .Range("c2:c3"), 0) + Application.Match(.Range("B" & irow), .Range("D2:D3")[/B][/COLOR], 0), 0)

Next irow

End With
End Sub

Wherteh two match criterias are added together

Unfortunately, this simple loop is giving silly results to.

 

[Akuini]

In your code in post 1, try using CLng:

                Sheet32.Range("Z" & RowNum).Value = Application.WorksheetFunction.Index(Sheet8.Range("AX2:AX5000").Value, Application.WorksheetFunction.Match(CLng(FormDate), Sheet8.Range("AQ2:AQ5000").Value, 0))

 

[Alex Blakenburg]

Can you get the index match formula working if you do it manually directly in Excel ?
I am a bit of a fan of using match with dates, since unlike the find method it is very forgiving of mixing and matching of date formats.
The only proviso would be if either value is Text.
(I don't believe CLng will make any difference)


I would definitely drop the .Value it produces Error 2042.

I am assuming that date in Sheet8.Range("AQ2:AQ5000") is unique, in which case using a dictionary is likely to be faster.

I would be interested in sorting our your Index-Match issue if you want to share an XL2BB of both Sheet 32 & Sheet 8 or a share-link to a sample worksheet.

 

[JeffGrant]

Hi Alex, I managed to find a work around, but would much rather find a direct solution.
Your are correct in that Clng did not make any difference.
I aslo did drop the .value before posting my questing and it did make a difference in that i recive the #N/A error instead of nothing. So that was sort of an improvment.
Unfortunatly Sheet8.Range("AQ2:AQ5000") is not unique, hence the double criteria in the match function.

As for sharing on XL2BB, I would love to but for some reason I have had a lot of trouble with it. I have installed and unstalled and reinstalled, many time, so now I don't bother with it I'm afraid.

As it stands right now, the Mini Sheet and Table Only are greyed out - Again

The work around I have right now goes like this:

Have you got a cup of coffee ready ?

I loop through the date - criteria 1 & venue - criteria 2.

use an Index/Match formula with multiple criteria in the sheet

Get the required result

Copy the returned result to where I need it.

End of cup of coffee

It is very slow, but it got me out of trouble today

 

[Alex Blakenburg]

If you are saying you have to match date & venue and all your users have MS 365 then you could use either XLookup or Filter instead of Index-Match.
A dictionary will also still work.

 

[RoryA]

Unfortunately, this simple loop is giving silly results to.

That's because you told it to add two separate Match results, not match two conditions at the same time. So even if the first match is position 2 and the second match is also position 2, you're passing position 4 to the Index function.

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