Excel VBA中使用AND+COUNTIF公式触发错误13的解决求助

问题原因 #

错误13（类型不匹配）的核心原因：

• 工作表中的AND函数可处理数组输入（数组公式逻辑），但VBA的Application.WorksheetFunction.And不支持数组参数。
• 代码中Range("C2:C5")是多单元格区域，与通配符拼接后生成数组，CountIf返回数组结果，WorksheetFunction.And无法识别该数组类型，因此报错。

额外注意：原数据中C5为空单元格，直接用*&$C$2:$C$5&*会生成**，会匹配任何非空单元格，导致结果不准确，需排除C列空值。

解决方案 #

方案1：用Application.And替代WorksheetFunction.And #

Application.And支持数组参数，能正确处理CountIf的数组结果：

Sub CI()
    ' 排除C列空单元格，避免无效匹配
    Dim checkNames As Range
    On Error Resume Next ' 防止C列全为空时报错
    Set checkNames = Range("C2:C5").SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    
    If Not checkNames Is Nothing Then
        ' 生成CountIf的数组结果
        Dim countResults As Variant
        countResults = Application.CountIf(Cells(2, 1), "*" & checkNames & "*")
        ' 判断所有结果是否都大于0
        Cells(7, 2).Value = Application.And(countResults)
    Else
        Cells(7, 2).Value = True ' 无检查项时默认返回True，可按需调整
    End If
End Sub

方案2：直接用Evaluate运行工作表公式 #

最简便的方式是调用Excel公式引擎，注意把公式中的分号换成逗号（VBA用逗号作为参数分隔符）：

Sub CI_Evaluate()
    ' 直接执行工作表逻辑，自动处理数组
    Cells(7, 2).Value = Evaluate("AND(COUNTIF(A2,""*""&$C$2:$C$5&""*""))")
    ' 若要排除C列空值，可修改公式为：
    ' Cells(7, 2).Value = Evaluate("AND(COUNTIF(A2,""*""&FILTER($C$2:$C$5,$C$2:$C$5<>"""")&""*""))")
End Sub

方案3：批量处理多行数据 #

如果要批量检查A2:A5所有行并填充B列结果：

Sub BatchCheckAllRows()
    Dim checkNames As Range
    On Error Resume Next
    Set checkNames = Range("C2:C5").SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    
    If checkNames Is Nothing Then Exit Sub
    
    Dim targetCell As Range
    For Each targetCell In Range("A2:A5")
        Dim countArr As Variant
        countArr = Application.CountIf(targetCell, "*" & checkNames & "*")
        targetCell.Offset(0, 1).Value = Application.And(countArr)
    Next targetCell
End Sub

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