VBA调用VLookup函数始终返回2042错误是什么原因？

错误原因 #

• 1. 数组结构和VLookup查找逻辑不匹配
     VLookup的核心规则是在传入区域/数组的第一列搜索匹配值，你定义的MonthMatrix是2行12列的结构，月份缩写存储在第一行而非第一列，VLookup无法匹配到目标值，返回代表#N/A的2042错误。
• 2. 返回列参数设置错误
     代码中VLookup的第三个参数设为1，即使匹配成功也只会返回第一列的月份缩写，你要获取对应的数字月份，需要将该参数改为2。
• 3. 数组定义存在语法错误
     Dim MonthMatrix(0 To 1, 0 To 11 As Variant 行缺少右括号，会触发语法报错，正确写法为Dim MonthMatrix(0 To 1, 0 To 11) As Variant。

修复后代码 #

Option Explicit

Sub GetMonthNumber()
    ' 补全数组定义的右括号
    Dim MonthMatrix(0 To 1, 0 To 11) As Variant
    Dim month As String
    Dim month_number As Variant
    
    MonthMatrix(0, 0) = "Jan"
    MonthMatrix(1, 0) = 1
    MonthMatrix(0, 1) = "Feb"
    MonthMatrix(1, 1) = 2
    MonthMatrix(0, 2) = "Mar"
    MonthMatrix(1, 2) = 3
    MonthMatrix(0, 3) = "Apr"
    MonthMatrix(1, 3) = 4
    MonthMatrix(0, 4) = "May"
    MonthMatrix(1, 4) = 5
    MonthMatrix(0, 5) = "Jun"
    MonthMatrix(1, 5) = 6
    MonthMatrix(0, 6) = "Jul"
    MonthMatrix(1, 6) = 7
    MonthMatrix(0, 7) = "Aug"
    MonthMatrix(1, 7) = 8
    MonthMatrix(0, 8) = "Sep"
    MonthMatrix(1, 8) = 9
    MonthMatrix(0, 9) = "Oct"
    MonthMatrix(1, 9) = 10
    MonthMatrix(0, 10) = "Nov"
    MonthMatrix(1, 10) = 11
    MonthMatrix(0, 11) = "Dec"
    MonthMatrix(1, 11) = 12
    
    month = "Sep"
    ' 转置数组为12行2列结构，让月份缩写落在第一列，返回列设为2
    month_number = Application.VLookup(month, Application.Transpose(MonthMatrix), 2, False)
    ' 测试输出
    MsgBox month_number
End Sub

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