VBA Run-time error 1004: Application-defined or object-defined error

Question

I'm just getting familiar with VBA and my code

xxLastrow = xLastRow+1
For x = 11 To xLastRow
For y = 12 To xxLastrow
If ActiveSheet.Cells(x, 2).Value = Cells(y, 2).Value Then
For z = 4 To xLastColumn
If ActiveSheet.Cells(y, z).Value <> "" Then  '(possible If Not IsEmpty(Cells(y, z).Value))
ActiveSheet.Cells(y, z).Select
Selection.Copy
ActiveSheet.Cells(x, z).Select
ActiveSheet.Paste
End If
Next z
End If
Next y
Next x

makes Run-time error 1004: Application-defined or object-defined error when the line "If ActiveSheet.Cells(y, z).Value <> "" Then" goes. All variables are integer (including lastrow/column). What's the problem here? I promise sea of cats for man who will try to help :)

Also I feel that the error is not on that line but on Cells(y, 2).Value or ActiveSheet.Cells(y, z).Select or ActiveSheet.Cells(x, z).Select — Siddharth Rout
xLastcolumn is numer of columns filled in previous steps. It was taken from another cycle (the same with xLastColumn). There is no error on Cells value or select, why should it be? — Seya
@SiddharthRout yep, sorry, slow internet :) so why there should be an error on cells selection and so on? arguments are numbers, I don't see any rules broken. I'd rather prefer to operate with range, but it's hard with cycle running - i don't want to insert function — Seya

user2140261 user2140261 · Accepted Answer · 2014-03-05T14:26:42

This i smoe of a comment than an answer, but as code looks ugly in a comment I thought I would post as answer.

You can accomplish your same results with a much more efficient code, the below code uses value = value instead of copy paste, as to avoid using the clipboard (that itself is a buggy slow process). It also does not loop every single column reducing your loops by (Row matches * Column Count) number of times, probably a lot.

Dim rngTest As Range
Dim rngCur As Range
Dim rngCOff As Range

Set rngTest = Range("B11:B" & xLastRow)

For Each rngCur In rngTest
    Set rngCOff = rngCur.Offset(1)
    If rngCur.Value = rngCOff.Value Then
       rngCOff.Offset(, 2).Resize(, xLastColumn - 4).Value = rngCur.Offset(, 2).Resize(, xLastColumn - 4).Value
    End If
Next rngCur

VBA Run-time error 1004: Application-defined or object-defined error

1 Answers