VBA实现基于动态数组填充单元格并追加未存在元素

以下是针对你的需求的完整解决方案，解决动态范围处理和新增元素追加的问题：

Sub UpdateResultToCells()
    Dim result As Variant
    ' 替换为你的REST API调用逻辑，这里用模拟数组示例
    result = Array(1, 2, 3, 4, 5, 6, 7, 8)
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' 改成你实际用的工作表名
    
    ' 获取A列最后一个非空行，实现动态范围
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 收集已存在的单元格值，用于快速判断重复
    Dim existingValues As Collection
    Set existingValues = New Collection
    
    If lastRow >= 1 Then
        Dim cell As Range
        For Each cell In ws.Range("A1:A" & lastRow)
            If Not IsEmpty(cell.Value) Then
                On Error Resume Next ' 忽略重复值的添加报错
                existingValues.Add cell.Value, Key:=CStr(cell.Value)
                On Error GoTo 0
            End If
        Next cell
    End If
    
    ' 过滤数组中的新增元素
    Dim newItems As Variant
    ReDim newItems(1 To UBound(result) + 1)
    Dim newCount As Long
    newCount = 0
    
    Dim i As Long
    For i = LBound(result) To UBound(result)
        On Error Resume Next
        existingValues.Item(CStr(result(i)))
        If Err.Number <> 0 Then ' 该值不在现有单元格中，属于新增
            newCount = newCount + 1
            newItems(newCount) = result(i)
        End If
        On Error GoTo 0
    Next i
    
    ' 批量写入新增元素到单元格末尾
    If newCount > 0 Then
        ReDim Preserve newItems(1 To newCount)
        ws.Cells(lastRow + 1, "A").Resize(newCount, 1).Value = Application.Transpose(newItems)
    End If
End Sub

关键逻辑说明 #

• 动态范围处理：
  用ws.Cells(ws.Rows.Count, "A").End(xlUp).Row自动获取A列最后一个非空行，不管数组长度多少，都能精准定位现有数据的边界，完全替代硬编码的范围（比如A1:A5）。初始空表时，lastRow会指向A1，所有数组元素都会被当作新增写入。

• 新增元素过滤与追加：
  利用Collection的Key唯一性特性，快速判断数组元素是否已存在于单元格中。遍历数组时，把未出现过的元素存入临时数组，最后通过Resize和Transpose批量写入到现有数据的下一行，比逐个单元格循环写入效率更高。

原代码问题修正 #

• 原代码中result(i) = cel.Value是反向赋值（把单元格值给数组，不是数组给单元格），新方案用批量写入彻底避免了这个逻辑错误。
• 移除了硬编码的单元格范围，改为动态获取最后一行，适配任意长度的数组。

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