VBA - ReDim Preserve 创建假数组元素

Question

这是一个 for 循环，用于查找范围内的值并创建充满匹配结果的新数组。问题是查看我在第一个 If 语句 AR2（i） 中看到的对象创建一个元素 AR2（0），该元素为空，然后将 AR2（1） 分配为 If Else 值。

Sub rt()
Dim AR0() As Variant, AR1() As Variant, AR2() As Variant
Dim WS0 As Worksheet, WS1 As Worksheet
Dim i As Integer, RW0 As Integer, RW1 As Integer
Dim C As Range
Set WS0 = Sheets("lookup")
Set WS1 = Sheets("centro")
RW1 = WS1.Cells(WS1.Rows.Count, "A").End(xlUp).row
AR0 = WS0.Range("A3:A28")
For i = 1 To UBound(AR0, 1)
    With WS1.Range("A2:A" & RW1)
        Set C = .find(AR0(i, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
        ReDim Preserve AR2(i)
        If Not C Is Nothing Then
            AR2(i) = "YES"
        Else
            AR2(i) = " - "
        End If
    End With
Next
WS0.Range("B3:B28") = WorksheetFunction.Transpose(AR2)
End Sub

Answer 1

错误是 AR2 在没有分配值的情况下创建元素 AR2（0）。解决方案是将 For 循环设置为 0 开始，将 AR0（i +1， 1） 设置为允许从循环位置 0 开始查找此值。谢谢！

    Sub rt()
    Dim AR0() As Variant, AR1() As Variant, AR2() As Variant
    Dim WS0 As Worksheet, WS1 As Worksheet
    Dim i As Integer, RW0 As Integer, RW1 As Integer
    Dim C As Range
    Set WS0 = Sheets("lookup")
    Set WS1 = Sheets("centro")
    RW1 = WS1.Cells(WS1.Rows.Count, "A").End(xlUp).row
    AR0 = WS0.Range("A3:A28")
    For i = 0 To UBound(AR0, 1) - 1
        With WS1.Range("A2:A" & RW1)
            Set C = .find(AR0(i + 1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
            ReDim Preserve AR2(i)
            If Not C Is Nothing Then
                AR2(i) = "YES"
            Else
                AR2(i) = " - "
            End If
        End With
    Next
    WS0.Range("B3:B28") = WorksheetFunction.Transpose(AR2)
    End Sub