使用数组替代VLOOKUP的VBA问题：数组赋值与写入工作表

我来帮你搞定这个问题！你遇到的<类型不匹配>报错，核心原因是**ArrOutput数组没有提前初始化固定维度**，反而在循环里反复用ReDim Preserve调整，导致数组状态不稳定。另外反复ReDim本身也会拖慢效率，完全没必要——毕竟你早就知道输出数组的大小了呀！

关键修改要点： #

• 提前初始化ArrOutput的维度：既然你已经通过UpperElement = UBound(ArrLookupValues)拿到了输出数组的行数，直接在循环前就把数组大小固定好，不用每次循环都调整
• 移除循环内的ReDim Preserve语句：避免数组维度频繁变化引发的类型错误
• 确保写入工作表时的范围引用明确：最好指定具体工作表，避免依赖ActiveSheet导致的意外

修正后的完整代码 #

Option Explicit
Sub testArray()
    Dim ArrLookupValues As Variant
    ArrLookupValues = Sheet1.Range("A1:A5") 'The Lookup Values
    
    Dim ArrLookupRange As Variant
    ArrLookupRange = Sheet1.Range("C1:C5") 'The Range to find the Value
    
    Dim ArrReturnValues As Variant
    ArrReturnValues = Sheet1.Range("D1:D5") 'The adjacent Range to return the Lookup Value
    
    Dim ArrOutput As Variant 'output array
    Dim UpperElement As Long
    UpperElement = UBound(ArrLookupValues)
    
    ' 提前初始化输出数组的固定维度，这是解决类型不匹配的核心
    ReDim ArrOutput(1 To UpperElement, 1 To 1)
    
    Dim i As Long
    For i = LBound(ArrLookupValues) To UBound(ArrLookupValues)
        Dim myVal As Variant
        myVal = ArrLookupValues(i, 1)
        
        Dim pos As Variant 'variant because it can return an error
        pos = Application.Match(myVal, ArrLookupRange, 0)
        
        Dim myVal2 As Variant
        If Not IsError(pos) Then
            myVal2 = ArrReturnValues(pos, 1)
            ArrOutput(i, 1) = myVal2 '直接给已初始化的数组位置赋值
        Else
            myVal2 = "Not Found"
            ArrOutput(i, 1) = myVal2
        End If
    Next i
    
    ' 写入工作表时明确指定Sheet1，避免ActiveSheet的不确定性
    Dim Destination As Range
    Set Destination = Sheet1.Range("B1")
    Destination.Resize(UBound(ArrOutput, 1), UBound(ArrOutput, 2)).Value = ArrOutput
End Sub

代码解释： #

1. 数组初始化：循环前用ReDim ArrOutput(1 To UpperElement, 1 To 1)直接创建一个和查找值数组行数一致的二维数组，这样数组从一开始就有明确的维度，赋值时不会出现类型不匹配
2. 循环赋值：去掉循环内的ReDim Preserve，直接给ArrOutput(i, 1)赋值，逻辑更清晰，效率也更高
3. 工作表引用：写入结果时指定Sheet1.Range("B1")，避免因为当前激活工作表变化导致的写入错误

这样修改后，你的代码就能正常把结果写入B1:B5区域了，而且大数据集下的效率会比VLOOKUP高很多！

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