Index with multiple match (Loop) when one of the Match is a Date ( VBA ) - Value Mismatch

[xs4amit]

Hi I am trying to do index match Loop through Columns first and then Rows with multiple criteria in VBA

My Source data is like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]email ID[/TD]
[TD]Attendance Status[/TD]
[/TR]
[TR]
[TD]22/08/18[/TD]
[TD]Bob22@tiptop.com[/TD]
[TD]Present[/TD]
[/TR]
[TR]
[TD]22/08/18[/TD]
[TD]Milan@tiptop.com[/TD]
[TD]Present[/TD]
[/TR]
[TR]
[TD]22/08/18[/TD]
[TD]Samuel@tiptop.com[/TD]
[TD]Present[/TD]
[/TR]
[TR]
[TD]23/08/18[/TD]
[TD]Bob22@tiptop.com[/TD]
[TD]Planned Leave[/TD]
[/TR]
[TR]
[TD]23/08/18[/TD]
[TD]Milan@tiptop.com[/TD]
[TD]Present[/TD]
[/TR]
[TR]
[TD]23/08/18[/TD]
[TD]Samuel@tiptop.com[/TD]
[TD]Present[/TD]
[/TR]
[TR]
[TD]24/08/18[/TD]
[TD]Bob22@tiptop.com[/TD]
[TD]Present[/TD]
[/TR]
[TR]
[TD]24/08/18[/TD]
[TD]Milan@tiptop.com[/TD]
[TD]Unplanned Leave[/TD]
[/TR]
</tbody>[/TABLE]


And my Destination Data should be like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Email IDs[/TD]
[TD]22/08/18[/TD]
[TD]23/08/18[/TD]
[TD]24/08/18[/TD]
[/TR]
[TR]
[TD]Bob22@tiptop.Com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Milan@tiptop.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Samuel@tiptop.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Where as i am using Code with Worksheet function

With Destination
.Range("B2:D4").Clear
For R = 2 To 4
For C = 2 To UBound(Arr2, 2)

.Cells(R, C) = .Application.WorksheetFunction.Index(Source.Range("A2:C30000"), _
.Application.WorksheetFunction.Match(.Cells(R, 1) & CDate(.Cells(1, C)), WS1.Range("B2:B30000") & WS1.Range("A2:A30000"), 0), 3)


Next C


Next R
.Activate
End With


End Sub

it is giving me Type Mismatch Error. Any help??

I am using Excel 2016 on Mac.