VBA使用Union生成的单元格区域能否粘贴？报错1004如何解决

错误根因 #

你遇到的1004错误主要由以下三个问题触发：

• 变量未重置：每轮处理新行时，G_Req、G_Add没有清空为初始的Nothing状态，不仅会携带上一行的冗余数据，若某行没有匹配颜色的单元格，会直接对空对象执行Copy操作触发报错
• 区域引用缺父对象：定义G_Res_Ra时Range没有指定所属工作表，当活动工作表不是Groepen时会读取错误范围，甚至出现跨表Union的非法场景
• 无空值判断：没有校验G_Req、G_Add是否存在有效单元格就直接调用Copy方法，无匹配单元格时必然报错

修复方案 #

Dim G_Each As Range
Dim G_Range As Range
Dim G_Res_A As Range
Dim G_ws As Worksheet
Dim I_ws As Worksheet
Dim G_Res_Ra As Range
Dim G_cell As Range
Dim G_Req As Range
Dim G_Add As Range
Dim I_Empty1 As Range
Dim I_Empty2 As Range

Set G_ws = Worksheets("Groepen")
Set I_ws = Worksheets("Invoer")
Set G_Range = G_ws.Range("A2", G_ws.Range("A2").End(xlDown))

For Each G_Each in G_Range
    ' 每轮循环先重置集合变量，避免携带上一轮数据
    Set G_Req = Nothing
    Set G_Add = Nothing
    
    Set G_Res_A = G_Each.Offset(0, 7)
    ' 补全Range的父对象引用，避免读取活动工作表数据
    Set G_Res_Ra = G_ws.Range(G_Res_A, G_Res_A.End(xlToRight))
            
    If I_ws.Range("F2") = "" Then
        Set I_Empty1 = I_ws.Range("F2")
    Else
        Set I_Empty1 = I_ws.Range("F2").End(xlToRight).Offset(0, 1)
    End If
            
    If I_ws.Range("G3") = "" Then
        Set I_Empty2 = I_ws.Range("G3")
    Else
        Set I_Empty2 = I_ws.Range("G3").End(xlToRight).Offset(0, 1)
    End If
            
    For Each G_cell In G_Res_Ra
        If G_cell.Interior.Color = RGB(255, 217, 102) Then
            If Not G_Req Is Nothing Then
                Set G_Req = Union(G_Req, G_cell)
            Else
                Set G_Req = G_cell
            End If
        Else
            If Not G_Add Is Nothing Then
                Set G_Add = Union(G_Add, G_cell)
            Else
                Set G_Add = G_cell
            End If
        End If
    Next G_cell
    
    ' 复制前先判断是否存在有效单元格
    If Not G_Req Is Nothing Then
        G_Req.Copy Destination:=I_Empty1
    End If
    If Not G_Add Is Nothing Then
        G_Add.Copy Destination:=I_Empty2
    End If
Next G_Each

额外优化提示 #

如果后续要调整写入规则，比如每行拆分的内容单独占一行而非横向追加，只需要修改I_Empty1、I_Empty2的定位逻辑即可，当前修复仅解决1004报错问题，原有写入逻辑保持不变。

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