使用条件格式计数列时ISNA(VLOOKUP)求值遇类型不匹配错误13

在VBA中执行ISNA(VLOOKUP(...))公式统计符合条件格式的单元格数量时，遇到以下问题：当ISNA返回TRUE（VLOOKUP未找到匹配值）时代码正常运行，返回FALSE（VLOOKUP找到匹配值）时触发类型不匹配错误13。原代码如下：

Sub CF_FAILS()

Dim cellValue As String
Dim cellCount As Long
Dim result As Boolean

'#1 cellValue Returns TRUE AND WORKS

    cellValue = "=ISNA(VLOOKUP($G6,API_RESPONSE,RCI.Item_Lookup,0))"
    result = Application.Evaluate(cellValue)
    If (result) Then
        cellCount = cellCount + 1
    End If

    ActiveSheet.range("A20").Value = result
    
'#2 cellValue Returns FALSE AND FAILS with a TYPE-MISMATCH error
    
    cellValue = "=ISNA(VLOOKUP($G9,API_RESPONSE,RCI.Item_Lookup,0))"
    result = Application.Evaluate(cellValue)  '<== FAILS HERE WITH TYPE-MISMATCH ERROR 13'

    If (result) Then
        cellCount = cellCount + 1
    End If
    
    ActiveSheet.range("A21").Value = result
    
End Sub

问题原因 #

Application.Evaluate返回的是Variant类型，虽然ISNA函数逻辑上返回布尔值，但当VLOOKUP找到匹配值时，Excel内部的类型处理可能导致返回的Variant无法直接隐式转换为Boolean类型，从而触发类型不匹配错误。

解决方法 #

方法1：修改变量类型并显式转换布尔值

将result变量的类型从Boolean改为Variant，判断时通过CBool()显式转换为布尔值，避免隐式转换错误：

Sub CF_FIXED_EVALUATE()

Dim cellValue As String
Dim cellCount As Long
Dim result As Variant ' 修改为Variant类型

'#1 正常处理TRUE情况
cellValue = "=ISNA(VLOOKUP($G6,API_RESPONSE,RCI.Item_Lookup,0))"
result = Application.Evaluate(cellValue)
If CBool(result) Then ' 显式转换为布尔值
    cellCount = cellCount + 1
End If
ActiveSheet.Range("A20").Value = result

'#2 修复FALSE情况的类型不匹配
cellValue = "=ISNA(VLOOKUP($G9,API_RESPONSE,RCI.Item_Lookup,0))"
result = Application.Evaluate(cellValue)
If CBool(result) Then
    cellCount = cellCount + 1
End If
ActiveSheet.Range("A21").Value = result

End Sub

方法2：直接使用WorksheetFunction方法（推荐）

绕过Application.Evaluate的字符串公式，直接调用WorksheetFunction.VLookup并通过错误捕获判断是否匹配，更高效且易维护：

Sub CF_FIXED_WORKSHEETFUNCTION()

Dim cellCount As Long
Dim lookupValue As Variant
Dim lookupResult As Variant

'#1 处理G6
lookupValue = ActiveSheet.Range("$G6").Value
On Error Resume Next ' 捕获VLOOKUP未找到的错误
lookupResult = Application.WorksheetFunction.VLookup(lookupValue, ActiveSheet.Range("API_RESPONSE"), RCI.Item_Lookup, False)
If Err.Number <> 0 Then
    ' 未找到匹配，ISNA返回TRUE
    cellCount = cellCount + 1
    ActiveSheet.Range("A20").Value = True
Else
    ' 找到匹配，ISNA返回FALSE
    ActiveSheet.Range("A20").Value = False
End If
On Error GoTo 0 ' 恢复错误捕获

'#2 处理G9
lookupValue = ActiveSheet.Range("$G9").Value
On Error Resume Next
lookupResult = Application.WorksheetFunction.VLookup(lookupValue, ActiveSheet.Range("API_RESPONSE"), RCI.Item_Lookup, False)
If Err.Number <> 0 Then
    cellCount = cellCount + 1
    ActiveSheet.Range("A21").Value = True
Else
    ActiveSheet.Range("A21").Value = False
End If
On Error GoTo 0

End Sub

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