How do I match the month & year value of the date field with string using VBA Macro?

Question

I have a sheet where the first column holds the dates which can be in any date format. I am required to pass the date in the string format of MMM-yy into a function to get the cell address, but I am ending up getting either Error 2042 or type mismatch as I am trying to compare string & date. Given the scenario how do I solve the type-casting issue? My function is shared below:

 Function getcellAddress(ByVal col As String, ByVal row As String) As Range
    Dim r, c As Variant
    Dim maxRowCount As Integer

    With ActiveSheet
        r = Application.Match(row, Format(.Columns("A"), "MMM-yy"), 0)
        c = Application.Match(col, .Rows(1), 0)
        
        'add new record when company not found
        If IsError(r) And IsAllOther = False Then
            r = 65636
            c = 256
        End If
        Set getcellAddress = .Cells(r, c)
        
    End With

End Function

And here's how I am calling this function in a Sub Procedure:

Dim lookUp As String
    lookUp = getcellAddress("A", "May-21").Offset(-1, 0).Address

Sample Date Column has dates like

    A
1 Jan-21
2 Feb-21
3 Mar-21
4 Apr-21
5 May-21 

The expected Output needs to be A5.

Answer 1

Once you change the dates as I mentioned in your previous question, try this simple code to achieve what you want. Change as applicable.

Option Explicit

Sub Sample()
    Dim r As Range
    
    Set r = getcellAddress("Sep-21", "A")
    
    If Not r Is Nothing Then
        MsgBox r.Address
    Else
        MsgBox "Not Found"
    End If
End Sub

Function getcellAddress(searchString As String, Col As String) As Range
    Dim CurrentRow As Variant
    
    CurrentRow = Application.Match(searchString, Columns(Col), 0)
    
    If Not IsError(CurrentRow) Then
        Set getcellAddress = Range(Col & CurrentRow)
    End If
End Function