如何使用VBA宏匹配日期字段年月值与字符串并解决类型不匹配问题

错误根因 #

• 核心错误在于Format(.Columns("A"), "MMM-yy")写法不符合VBA语法：Format函数只能处理单个值，无法直接作用于整列单元格，返回的结果不是预期的格式化字符串数组，导致Match函数无法命中目标，返回Error 2042类型不匹配错误。
• 额外问题：参数名row是VBA内置关键字，容易引发冲突；调用时的Offset(-1, 0)会使结果上移一行，和预期输出A5不符。

函数代码 #

Function getcellAddress(ByVal targetCol As String, ByVal targetDateStr As String) As Range
    Dim r As Variant, c As Variant
    Dim formatArr As Variant

    With ActiveSheet
        ' 生成A列所有值格式化为英文MMM-yy的数组，规避区域语言问题
        formatArr = Application.Evaluate("INDEX(TEXT(A:A,""[$-en-US]MMM-yy""),)")
        ' 匹配目标日期字符串
        r = Application.Match(targetDateStr, formatArr, 0)
        ' 匹配目标列
        c = Application.Match(targetCol, .Rows(1), 0)
        
        ' 原有未匹配新增逻辑（注意IsAllOther变量需要提前声明赋值）
        If IsError(r) And IsAllOther = False Then
            r = 65636
            c = 256
        End If
        Set getcellAddress = .Cells(r, c)
    End With
End Function

调用代码 #

Dim lookUp As String
' 去掉Offset(-1,0)避免结果上移一行
lookUp = getcellAddress("A", "May-21").Address

• 用Application.Evaluate + INDEX + TEXT组合生成整列格式化后的字符串数组，确保Match函数可以正常比较字符串类型的日期值
• 格式字符串增加[$-en-US]前缀，强制使用英文月份缩写，不受系统区域语言设置影响
• 调整参数名避免和VBA内置关键字冲突，减少未知错误
• 移除调用时的Offset(-1,0)，符合输出A5的预期

内容的提问来源于stack exchange，提问作者Starky