VBA：使用IF语句检查单元格/区域值是否在数组的报错解决

Got it, let's break down why you're hitting that type mismatch error and how to fix it—plus a way to handle bulk processing efficiently, since that's way better for performance.

First, the root cause of your error: when you assign Range.Value to Ar1 or Ar2, you get a 2-dimensional variant array (even for a single column, it’s structured as (1 to row_count, 1 to 1)). Comparing a single cell value directly to this entire 2D array will never work—they’re completely different data types.

Let’s walk through solutions for both single-value checks and bulk processing:

1. Checking a Single Value Against Ar1/Ar2 #

Option 1: Use a Helper Function to Traverse the Array #

This is reliable and works for any array size. Create a reusable function to check if a value exists in your 2D column array:

Function IsInArray(searchVal As Variant, arr As Variant) As Boolean
    Dim i As Long
    ' Handle 2D single-column arrays (from Range.Value)
    If UBound(arr, 2) = 1 Then
        For i = LBound(arr, 1) To UBound(arr, 1)
            If arr(i, 1) = searchVal Then
                IsInArray = True
                Exit Function ' Exit early once found
            End If
        Next i
    End If
    IsInArray = False
End Function

Then call it in your main code:

Dim targetVal As Variant
targetVal = Workbooks("workbook1.xlsx").Sheets("Sheet1").Range("A" & LastRowH).Value

If IsInArray(targetVal, Ar1) Then
    ' Do your first action here (e.g., MsgBox "Found in Ar1")
ElseIf IsInArray(targetVal, Ar2) Then
    ' Do your second action here (e.g., MsgBox "Found in Ar2")
Else
    ' Handle values not in either array
End If

Option 2: Use Application.Match (No Helper Function Needed) #

Excel’s Match function can search directly in arrays. Just wrap it in IsError to handle cases where the value isn’t found:

Dim targetVal As Variant
targetVal = Workbooks("workbook1.xlsx").Sheets("Sheet1").Range("A" & LastRowH).Value

If Not IsError(Application.Match(targetVal, Ar1, 0)) Then
    ' Action for Ar1 matches
ElseIf Not IsError(Application.Match(targetVal, Ar2, 0)) Then
    ' Action for Ar2 matches
Else
    ' No match found
End If

2. Efficient Bulk Processing (Check an Entire Range) #

Looping through each cell individually is slow—VBA hates repeated worksheet interactions. Instead, use dictionaries (for lightning-fast lookups) and load all data into arrays first:

Sub BulkProcessMatchingValues()
    Dim Ar1() As Variant, Ar2() As Variant
    Dim dictAr1 As Object, dictAr2 As Object
    Dim lastRowWB2 As Long, lastRowWB1 As Long
    Dim targetArr() As Variant
    Dim i As Long
    
    ' Initialize dictionaries (fast lookup storage)
    Set dictAr1 = CreateObject("Scripting.Dictionary")
    Set dictAr2 = CreateObject("Scripting.Dictionary")
    
    ' Get last row for workbook2's sheets (adjust if your LastRow logic differs)
    With Workbooks("workbook2.xlsx")
        lastRowWB2 = .Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
        Ar1 = .Sheets("Sheet1").Range("A2:A" & lastRowWB2).Value
        Ar2 = .Sheets("Sheet2").Range("A2:A" & lastRowWB2).Value
    End With
    
    ' Populate dictionaries with values from Ar1/Ar2 (auto-deduplicates too)
    For i = LBound(Ar1, 1) To UBound(Ar1, 1)
        If Not dictAr1.Exists(Ar1(i, 1)) Then
            dictAr1.Add Ar1(i, 1), True
        End If
    Next i
    
    For i = LBound(Ar2, 1) To UBound(Ar2, 1)
        If Not dictAr2.Exists(Ar2(i, 1)) Then
            dictAr2.Add Ar2(i, 1), True
        End If
    Next i
    
    ' Load target range from workbook1 into an array (avoids slow cell-by-cell loops)
    With Workbooks("workbook1.xlsx").Sheets("Sheet1")
        lastRowWB1 = .Cells(Rows.Count, "A").End(xlUp).Row
        targetArr = .Range("A2:A" & lastRowWB1).Value
    End With
    
    ' Process each value in the target array
    For i = LBound(targetArr, 1) To UBound(targetArr, 1)
        If dictAr1.Exists(targetArr(i, 1)) Then
            ' Example action: Color column B red for Ar1 matches
            Workbooks("workbook1.xlsx").Sheets("Sheet1").Cells(i + 1, "B").Interior.Color = vbRed
        ElseIf dictAr2.Exists(targetArr(i, 1)) Then
            ' Example action: Color column B blue for Ar2 matches
            Workbooks("workbook1.xlsx").Sheets("Sheet1").Cells(i + 1, "B").Interior.Color = vbBlue
        End If
    Next i
    
    ' Clean up objects
    Set dictAr1 = Nothing
    Set dictAr2 = Nothing
End Sub

Why This Is Better: #

• Dictionaries let you check for a value in constant time (O(1)), which is way faster than looping through an array (O(n)) for large datasets.
• Loading ranges into arrays minimizes interactions with the worksheet—this is the single biggest way to speed up VBA code.

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