VBA检查数组值是否在指定范围时出现类型不匹配错误求助

问题根源 #

你遇到的「类型不匹配」错误，核心原因是从Range读取的数组是二维数组——哪怕你只取了单列，Range.Value返回的数组结构都是arr(行号, 列号)，而你用arr(j)去访问，相当于试图把二维数组当成一维数组用，自然触发类型不匹配。

修复方案1：修正数组访问逻辑 #

直接改数组的访问方式，同时统一指定工作表对象（避免ActiveSheet和目标工作表不一致导致的隐性错误）：

Dim arr As Variant
Dim ws As Worksheet
Dim i As Long, j As Long

' 先指定目标工作表，避免ActiveSheet切换导致的问题
Set ws = Workbooks("test.xlsx").Worksheets("test")

' 读取F列数据到二维数组
arr = ws.Range("F2:F" & ws.Range("F1").End(xlDown).Row).Value

' 遍历G列数据，注意所有Range/Cells都要指定ws
For i = 2 To ws.Range("A2").End(xlDown).Row
    ' 遍历数组时，要指定第二维为1（因为是单列）
    For j = 1 To UBound(arr, 1)
        ' 加空值判断，避免空单元格比较出错
        If Not IsEmpty(arr(j, 1)) And arr(j, 1) = ws.Cells(i, 7).Value Then
            ws.Cells(i, 7).Interior.Color = RGB(180, 198, 231)
            ' 找到匹配后退出内层循环，提升效率
            Exit For
        End If
    Next j
Next i

优化方案2：用Dictionary提升效率（推荐） #

如果数据量较大，嵌套循环的效率会很低，用Dictionary存储数组里的值，然后直接判断G列单元格是否在字典中，速度快很多：

Dim arr As Variant
Dim ws As Worksheet
Dim i As Long
Dim dict As Object

Set ws = Workbooks("test.xlsx").Worksheets("test")
Set dict = CreateObject("Scripting.Dictionary")

' 读取F列数据并存入字典（自动去重）
arr = ws.Range("F2:F" & ws.Range("F1").End(xlDown).Row).Value
For i = 1 To UBound(arr, 1)
    If Not IsEmpty(arr(i, 1)) Then
        dict(CStr(arr(i, 1))) = True
    End If
Next i

' 遍历G列，判断是否在字典中
For i = 2 To ws.Range("A2").End(xlDown).Row
    If dict.Exists(CStr(ws.Cells(i, 7).Value)) Then
        ws.Cells(i, 7).Interior.Color = RGB(180, 198, 231)
    End If
Next i

额外注意事项 #

• 所有涉及单元格的操作都要指定工作表对象ws，否则默认用ActiveSheet，容易出现数据错位
• 用CStr()把值转成字符串再比较，可以避免数字/文本类型不匹配的问题（比如单元格是文本型数字，数组里是数值型）
• 加IsEmpty判断，避免空单元格参与比较导致的错误

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