VBA Excel. Run-time error '1004'. Application-defined or operation-defined error

Question

I have a big problem trying to make this script works. I need to create a script on vba-excel that compares two lists that are two ranges of cells, and that if a value from the first list is not found in the second, copy it to the end of the second list and paste the position of a nearby cell the cell where it is. But i'm getting the Run-time error '1004': Application-defined or operation-defined error. My script is as follows:

 Dim CellA As Object
 Dim ListOLD As Range
 Dim ListNEW As Range
 Dim FinalA As Long
 Dim FinalC As Long


 FinalA = Worksheets("Sheet1").Range("K2").End(xlToRight).Column
 FinalC = Worksheets("Sheet2").Range("A9").End(xlDown).Row

  Set ListOLD = Worksheets("Sheet1").Range(Cells(11, 2), Cells(FinalA, 2))
  Set ListNEW = Worksheets("Sheet2").Range("A9:A" & FinalC)

  For Each CellA In ListNEW
 If Application.WorksheetFunction.CountIf(ListOLD, CellA) = 0 Then
  Worksheets("Sheet2").Select
  Worksheets("Sheet2").Range(CellA.Column, CellA.Row).Select
  Selection.Copy
  Worksheets("Sheet1").Range(Cells(11, 2), Cells(FinalA, 2)).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
   False, Transpose:=True
   Application.CutCopyMode = False
  End If
  Next Celda
End Sub

I'm pretty new to this, if anyone can give me a hand it would help me a lot. I'm working on this all day!!! Ty

It would be worth you reading https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba If the error is on the `Set ListOLD = ` line it's because you haven't added a sheet reference to all your `Cells`. — SJR, May 10 '20 at 19:00

score 1 · Accepted Answer · answered May 10 '20 at 19:07

The problem is that the Cells are not referred to the correct worksheet here:

Set ListOLD = Worksheets("Sheet1").Range(Cells(11, 2), Cells(FinalA, 2))

The problem is that in this case above, the "Parent" of the Cells(11,2) and the other one is either the ActiveSheet (if the code is insde a module) or the worksheet, in which the code is present. This actually quite a standard error, with similar questions here and here.

E.g. it should be like this:

Set ListOLD = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(11, 2), Worksheets("Sheet1").Cells(FinalA, 2))

Or it could be made a bit smaller, like this:

With Worksheets("Sheet1")
    Set ListOLD = .Range(.Cells(11, 2), .Cells(FinalA, 2))
End With

VBA Excel. Run-time error '1004'. Application-defined or operation-defined error

1 Answers1