比较两张纸中的一列，并在缺少的地方添加值

Question

我正在寻找比较 A 列从工作表("RETRTnew"(到列 A 到工作表("RT"(。我希望代码进行检查，如果 RT 中缺少 RETRTnew 中的值，那么我希望添加一行并从上面的行复制数据并粘贴为公式。然后仅将缺失值添加到 A 列。我正在使用以下代码，但它给出了

"未设置块变量"错误

在

Set xCopy = .Range(c, c.Offset(0, 1))

---

Sub ExportData()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Set rng_ID = Sheets("RT").Range("A96:A5000") 'set ID range on sheet2
Set Rng = Sheets("RETRTnew").Range("A2", Sheets("RETRTnew").Range("A2").End(xlDown)) 'set ID range on sheet1
With Sheets("RT")
For Each Cell In Rng 'loop to each cell in ID range on sheet1
Set c = rng_ID.Find(Cell.Value, lookat:=xlWhole) 'find if the cell value is in ID range on sheet2
If c Is Nothing Then 'if not found
Set xCopy = .Range(c, c.Offset(0, 1))
c.Offset(1, 0).EntireRow.Insert 'insert entire row below the found cell
c.Offset(0, 0).EntireRow.Copy
c.Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas
xCopy.Copy Destination:=c.Offset(1, 0) 'copy the range above then paste

Else 'if  found
End If
Next Cell
End With

End Sub

在 BigBen 的建议下，我更改了代码，现在将缺少的单元格值添加到工作表 RT 中数据集的末尾。但是，我想从上面的行中引入公式，但是当尝试复制粘贴上面的行时，由于某种原因，它没有复制粘贴公式。这背后的原因可能是什么？

For Each Cell In Rng 'loop to each cell in ID range on sheet1
oCost = Cell.Offset(0, 8).Value 'set the cost value
Set c = rng_ID.Find(Cell.Value, lookat:=xlWhole) 'find if the cell value is in ID range on sheet2
If Not c Is Nothing Then 'if found

Else 'if not found
Set Ofill = Sheets("RT").Range("A5000").End(xlUp).Offset(1, 0) 'set the last blank cell in column B sheet2
Ofill.Offset(1, 0).EntireRow.Insert
Ofill.Offset(1, 0).EntireRow.Copy
Ofill.Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas
Ofill.Value = Cell.Value 'fill the ID
End If

Answer 1

以下内容对我有用：

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Set rng_ID = Sheets("RT").Range("A96:A5000") 'set ID range on sheet2
Set Rng = Sheets("RETRTnew").Range("A2", Sheets("RETRTnew").Range("A2").End(xlDown)) 'set ID range on sheet1
With Sheets("RT")
For Each Cell In Rng 'loop to each cell in ID range on sheet1
oCost = Cell.Offset(0, 8).Value 'set the cost value
Set c = rng_ID.Find(Cell.Value, lookat:=xlWhole) 'find if the cell value is in ID range on sheet2
If Not c Is Nothing Then 'if found

Else 'if not found
Set Ofill = Sheets("RT").Range("A5000").End(xlUp).Offset(1, 0) 'set the last blank cell in column B sheet2
Ofill.Offset(1, 0).EntireRow.Insert
Ofill.Offset(1, 0).EntireRow.Copy
Ofill.Offset(0, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas
Ofill.Value = Cell.Value 'fill the ID
End If
Next Cell
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub