Excel VBA -查找单元格并添加到动态数组

Sub FindAndAddToDynamicArray()
    Dim targetValue As String
    Dim targetRange As Range
    Dim foundCell As Range
    Dim dynamicArray() As Variant
    Dim i As Integer
    
    targetValue = "目标值"
    Set targetRange = ActiveSheet.UsedRange
    
    ReDim dynamicArray(1 To 1) ' 初始化动态数组
    
    Set foundCell = targetRange.Find(targetValue)
    
    If Not foundCell Is Nothing Then
        dynamicArray(1) = foundCell.Value ' 将找到的值添加到动态数组中
        Do
            Set foundCell = targetRange.FindNext(foundCell)
            If Not foundCell Is Nothing Then
                i = i + 1
                ReDim Preserve dynamicArray(1 To i) ' 扩展动态数组
                dynamicArray(i) = foundCell.Value
            End If
        Loop While Not foundCell Is Nothing And foundCell.Address <> targetRange.Cells(1, 1).Address
    End If
    
    ' 遍历动态数组中的值
    For i = 1 To UBound(dynamicArray)
        MsgBox dynamicArray(i)
    Next i
End Sub