VBA使用Like比较字符串时引用单元格输入报类型不匹配问题

错误原因 #

• 核心问题：Criteria = Range("B7") 没有显式指定工作表，VBA默认读取当前活动工作表的B7单元格值，如果活动工作表不是你存放搜索条件的工作表，或者B7单元格本身包含错误值（如#N/A、#VALUE!等），将错误值赋值给声明为String类型的Criteria变量时就会触发类型不匹配错误。
• 隐藏问题1：每次匹配到结果执行ReDim ArrOutput(1 To x)时没有加Preserve关键字，之前存入数组的匹配结果会被全部清空，最终数组只会保留最后一条匹配结果。
• 隐藏问题2：直接将数组赋值给单个单元格Range("B9") = ArrOutput不符合VBA数组输出规则，要么只能输出第一个元素，要么触发错误。

修复方案 #

1. 读取单元格值时先做错误校验，同时显式指定工作表，避免读取到错误值；
2. 数组扩容时加Preserve关键字保留已有数据；
3. 输出数组时如果要展示所有匹配结果，可以转置后输出到多行区域，或者拼接为字符串输出到单个单元格。

修复后完整代码 #

Dim StrName As Range
Dim Criteria As String
Dim ArrOutput() As Variant
Dim x As Integer
Dim Rng As Range
Dim wsInput As Worksheet, wsRef As Worksheet

' 显式指定工作表，按需修改为你实际的工作表名称
Set wsInput = ThisWorkbook.Sheets("输入表") ' 存放搜索条件B7的工作表
Set wsRef = ThisWorkbook.Sheets("Reference")

' 先判断B7单元格是否为错误值，避免类型不匹配
If Not IsError(wsInput.Range("B7").Value) Then
    Criteria = CStr(wsInput.Range("B7").Value)
Else
    MsgBox "搜索条件单元格存在错误，请检查B7的值", vbCritical
    Exit Sub
End If

x = 0
Set Rng = wsRef.Range("A2:A596")

For Each StrName In Rng
    If Not IsEmpty(StrName.Value) And StrName.Value Like "*" & Criteria & "*" Then
        x = x + 1
        ReDim Preserve ArrOutput(1 To x) ' 加Preserve保留已有数组内容
        ArrOutput(x) = StrName.Value
    End If
Next StrName

' 输出结果，此处示例是将所有匹配结果用换行拼接后输出到B9
If x > 0 Then
    wsInput.Range("B9").Value = Join(ArrOutput, vbCrLf)
    ' 如果需要输出到多行，可以替换为下面的代码：
    ' wsInput.Range("B9").Resize(x,1).Value = Application.Transpose(ArrOutput)
Else
    wsInput.Range("B9").Value = "未找到匹配结果"
End If

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