Worksheets(n).Range.... returns runtime error 1004

Question

VBA Runtime Error 1004 “Application-defined or Object-defined error”

is returned when I run this code. I searched Google / Stack Overflow and they all tell me to specify the range but funny thing is that the error occurred AFTER I specified range. (before the modification, it did work but returned wrong result)

Worksheets(2).Range(Cells(finderA.Row, rng2.Column), Cells(finderA.Row, rng2.Columns.Count + rng2.Column - 1)).Copy finalWS.Cells(rowCounter, rng1.Columns.Count + 2)

--> This is the line returning error.

The whole code is a part of my long lines for checking omissions between two data, by comparing specific common value.

What I'm basically trying to do in this code is to loop one range through the other and copy-paste the matching rows in order (i.e, I'm making sheet3 out of sheet 1,2.)

Dim rowCounter As Integer
rowCounter = 1

Dim eachCellA As Range, eachCellB As Range
Dim eachCellAa As Range, eachCellBb As Range
Dim StrFirstAdd As String, StrAdd As String
Dim finderA As Range, finderB As Range

For Each eachCellA In addCellRngA
    With addCellRngB
        Set finderA = .Find(eachCellA.Value, LookIn:=xlValues, Lookat:=xlWhole)
    End With

        If Not finderA Is Nothing Then

            eachCellA.EntireRow.Copy finalWS.Cells(rowCounter, 1)

            StrFirstAdd = finderA.Address
            StrAdd = finderA.Address

            Do
                Worksheets(2).Range(Cells(finderA.Row, rng2.Column), Cells(finderA.Row, rng2.Columns.Count + rng2.Column - 1)).Copy finalWS.Cells(rowCounter, rng1.Columns.Count + 2)  **##--> Debugger stops here, returns error.** 
                Set finderA = addCellRngB.FindNext(finderA)
                StrAdd = finderA.Address
                rowCounter = rowCounter + 1
            Loop While Not finderA Is Nothing And StrAdd <> StrFirstAdd

        Else

            eachCellA.EntireRow.Copy finalWS.Cells(rowCounter, 1)
            rowCounter = rowCounter + 1

        End If

Next

This is the code and all the values (finderA.Row , rng2.Column , rng2.Columns.count etc) are NOT empty and have right values.

I thought to paste the whole lines were unnecessary but if you feel like the reason is not within the code, let me know I'll be more than happy to provide the whole code.

SJR · Accepted Answer · 2017-05-19T17:25:22.780

4

Replace

Worksheets(2).Range(Cells(finderA.Row, rng2.Column), Cells(finderA.Row, rng2.Columns.Count + rng2.Column - 1)).Copy finalWS.Cells(rowCounter, rng1.Columns.Count + 2)

with

With Worksheets(2)
    .Range(.Cells(finderA.Row, rng2.Column), .Cells(finderA.Row, rng2.Columns.Count + rng2.Column - 1)).Copy finalWS.Cells(rowCounter, rng1.Columns.Count + 2) ' **##--> Debugger stops here, returns error.**
End With

You must qualify all range references, including Cells, otherwise the latter may be referring to a different sheet (whichever is active when you run the code).

You could also remove this line from the If as you do it in either case

eachCellA.EntireRow.Copy finalWS.Cells(rowCounter, 1)

and change your Loop condition to

Loop While StrAdd <> StrFirstAdd

edited May 19 '17 at 17:25

answered May 19 '17 at 17:15

SJR

22,986
6
18
26

related: [Is the . in .Range necessary when defined by .Cells?](http://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) – May 19 '17 at 17:16
So I'd conclude that to be on the safe side use the dot everywhere and you can't go wrong. I'vebeen picked up here before for not having a dot in front of Rows.Count. Mostly you don't need it, but to be on the safe side ... – SJR May 19 '17 at 17:20
1

YOU HAVE ENDED MY AGONY. I HAVE SPENT 4 DAYS WORKING ON THIS AND AFTER MY FINAL MODIFICATION WITH YOUR CODE IT IS NOW OVER. I don't know how I can thank you more. Maybe I should study hard on this and hopefully someday solve somebody else's trouble in this community. Thank you SJR and God Bless You. – zebralamy May 19 '17 at 17:49
My pleasure. To spare the pain, next time head straight for Rubberduck! – SJR May 19 '17 at 18:02

Worksheets(n).Range.... returns runtime error 1004

1 Answers1