Unable to get Match property of the WorksheetFunction class

Question

What I'm trying to do is based on a Cell oSht_Input.Cells(Rows, 7) , I want to find a match in another Sheet's (periodSheet) Column A, and get the corresponding value based on a given column.

I have tried combining the usage of .Index and .Match to do this. the .Index works for me, however I'm getting and error that says

Run-time error '1004': Unable to get the Match property of the WorksheetFunction class.

I have also tried doing Application.Match in the codes, but that would return me a #N/A Value instead.

What am I doing wrong?

I'm a little bit confused the usage of Application.Match and Application.WorksheetFunction.Match.

Set oSht_Input = Worksheets(outSheet)
Set periodSheet = Worksheets("PeriodMetadata")
lastRow = oSht_Input.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For Rows = 2 To lastRow
    With Application.WorksheetFunction
        dateCell = oSht_Input.Cells(Rows, 7)
        If rollupDataFile.GroupByPeriod Like "Week*" Then
            If rollupDataFile.GroupByPeriod Like "*Sunday" Then
                oSht_Input.Cells(Rows, 16).value = .Index(periodSheet.Range("B:H"), .Match(dateCell, periodSheet.Range("A:A"), 0), 1)
            ElseIf rollupDataFile.GroupByPeriod Like "*Monday" Then                    
                oSht_Input.Cells(Rows, 16).value = .Index(periodSheet.Range("B:H"), .Match(dateCell, periodSheet.Range("A:A"), 0), 2)

.... code continues

Edit: Decided to add in a little more context for better clarification based on the initial feedback.

dateCell will take in the value of a cell, which is definitely a date value. The Column A in periodSheet contains EVERY single date from January 1st 2000 to December 31st 2020, essentially covering all possible dates. You can safely assume that the value from the dateCell will be within this range of 20 years.

1. This is the oSht_Input, where Column G is the date

2. This is the PeriodMetadata sheet, where I am trying to match to Column A (which contains every single date of year 2000-2020 before finding the value based on the .index function.

Answer 1

Use CLng() or .Value2 instead when working with dates:

Sub SO()
'// C3 = "03/02/2015"
'// A1:A14 = "01/02/2015" to "14/02/2015"
'// All cells formatted as dates

'// This will NOT work:
Debug.Print WorksheetFunction.Match(Range("C3").Value, Range("A1:A14"), 0)

'// This WILL work:
Debug.Print WorksheetFunction.Match(Range("C3").Value2, Range("A1:A14"), 0)

'// This WILL also work:
Debug.Print WorksheetFunction.Match(CLng(Range("C3").Value), Range("A1:A14"), 0)

End Sub

This is because of the way Excel stores dates as numbers - something which sometimes has to be accounted for in VBA