使用VBA的Match等搜索函数时出现类型不匹配错误该如何解决？

报错&匹配不全的核心原因 #

• Application.Match未匹配到目标值时会返回错误值，直接赋值给字符串/数值类型变量就会触发运行时错误13：类型不匹配
• Match方法仅能返回第一个匹配项的位置，天然不支持获取所有匹配结果
• 现有代码缺少End If闭合判断块，存在语法问题

基础修复版本（解决报错+匹配两个后缀的目标值） #

Private Sub CHK1_change()
    Dim sh6 As Worksheet
    Set sh6 = ThisWorkbook.Sheets("OOS")
    Dim ILS1 As Variant ' 用Variant接收可能的错误返回值
    Dim ILS2 As Variant
    Dim ILSA1 As String, ILSA2 As String ' 补充变量声明避免隐式变体问题

    ILSA1 = Me.txtILS.Value & "-WF"
    ILSA2 = Me.txtILS.Value & "-F"

    ' 匹配WF后缀
    ILS1 = Application.Match(ILSA1, sh6.Range("B:B"), 0)
    If Not IsError(ILS1) Then ' 先判断是否匹配成功，避免类型错误
        If sh6.Range("F" & ILS1).Value <> "" Then
            Me.txtILSA1.Value = "WF"
        End If
    End If

    ' 匹配F后缀
    ILS2 = Application.Match(ILSA2, sh6.Range("B:B"), 0)
    If Not IsError(ILS2) Then
        If sh6.Range("F" & ILS2).Value <> "" Then
            Me.txtILSA2.Value = "F"
        End If
    End If
End Sub

进阶版本（获取所有匹配结果，适合同个后缀存在多条数据的场景） #

如果同一个后缀可能存在多行匹配结果，用Find方法循环遍历所有匹配项即可，示例代码：

Private Sub CHK1_change()
    Dim sh6 As Worksheet
    Set sh6 = ThisWorkbook.Sheets("OOS")
    Dim targetWF As String, targetF As String
    Dim findRng As Range, firstFind As String
    Dim wfList As String, fList As String ' 存储所有匹配结果，多个值可自定义分隔符

    targetWF = Me.txtILS.Value & "-WF"
    targetF = Me.txtILS.Value & "-F"

    ' 查找所有WF后缀匹配项
    Set findRng = sh6.Range("B:B").Find(targetWF, LookIn:=xlValues, lookat:=xlWhole)
    If Not findRng Is Nothing Then
        firstFind = findRng.Address
        Do
            If sh6.Range("F" & findRng.Row).Value <> "" Then
                wfList = wfList & "WF" & ";" ' 多个结果用分号分隔，可根据需求调整
            End If
            Set findRng = sh6.Range("B:B").FindNext(findRng)
        Loop While Not findRng Is Nothing And findRng.Address <> firstFind
    End If
    Me.txtILSA1.Value = wfList

    ' 查找所有F后缀匹配项
    Set findRng = sh6.Range("B:B").Find(targetF, LookIn:=xlValues, lookat:=xlWhole)
    If Not findRng Is Nothing Then
        firstFind = findRng.Address
        Do
            If sh6.Range("F" & findRng.Row).Value <> "" Then
                fList = fList & "F" & ";"
            End If
            Set findRng = sh6.Range("B:B").FindNext(findRng)
        Loop While Not findRng Is Nothing And findRng.Address <> firstFind
    End If
    Me.txtILSA2.Value = fList
End Sub

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