使用Application.Match匹配字符串与数组元素时遇类型不兼容错误求助

原代码的核心问题 #

• 数组未初始化：arr一开始是空状态，Application.Match无法对空数组执行匹配操作，直接触发错误。
• 变量类型错配：mtch被定义为String，但Application.Match返回值是变体类型——匹配成功返回数字索引，失败返回错误值，字符串类型无法接收错误值，导致类型不兼容。
• 变量声明不严谨：Dim i, j, k As Long中仅k为长整型，i和j默认是变体类型，需逐个明确类型。
• 判断逻辑错误：用IsNumeric(mtch)判断匹配失败不准确，应直接用IsError(mtch)检测错误值；且数组添加元素时错误赋值为mtch，实际应添加当前遍历的srch。
• 行号获取有坑：.Range("A1").End(xlDown).Row若A1下方无数据，会直接跳到工作表最后一行，导致无效循环。

修正后的代码 #

Sub Test_4()
    Dim i As Long
    Dim arr As Variant
    Dim srch As String
    Dim mtch As Variant ' 改为Variant类型，可接收Match返回的数字或错误值
    
    With Worksheets("table1")
        ' 安全获取A列最后一行有数据的行号
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        ' 初始化数组，避免空数组导致Match报错
        If lastRow >= 1 Then
            arr = Array(.Range("A1").Value)
            
            ' 从第2行开始遍历（第1行已存入数组）
            For i = 2 To lastRow
                srch = .Range("A" & i).Value
                mtch = Application.Match(srch, arr, 0)
                
                ' 匹配失败则将元素添加至数组末尾
                If IsError(mtch) Then
                    ReDim Preserve arr(UBound(arr) + 1)
                    arr(UBound(arr)) = srch
                End If
            Next i
        End If
    End With
End Sub

关键修改说明 #

1. 数组初始化：先将A列第一个值存入数组，确保arr为有效一维数组，避免Match对空数组操作报错。
2. 变量类型调整：mtch改为Variant，可同时接收匹配成功的数字索引和匹配失败的错误值。
3. 行号优化：用Cells(.Rows.Count, "A").End(xlUp).Row获取最后一行数据行号，避免空行导致的无效循环。
4. 逻辑修正：用IsError(mtch)判断元素是否不存在，添加元素时赋值为当前的srch，修正原代码的赋值错误。
5. 变量声明规范：明确i为长整型，避免变体类型带来的隐性问题。

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