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
– Siddharth Rout, Commented Mar 5, 2014 at 13:44
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
– Seya, Commented Mar 5, 2014 at 13:48
@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
– Seya, Commented Mar 5, 2014 at 13:52

user2140261 · Accepted Answer · 2014-03-05 14:26:42Z

0

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

answered Mar 5, 2014 at 14:26

user2140261

8,0037 gold badges35 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Seya Over a year ago

Thanks a lot. If we don't set area for range, it becomes a cell by default? Or why haven't you mentioned rngCur?

user2140261 Over a year ago

@Seya I do not understand your comment. rngCur, is a Range, and it is used inside of the loop. For Each rngCur and If rngCur.Value and one last time on the right hand side of the equal sign inside the if. rngCur, is the Current Range/Cell when looping all of the cells inside of Test Range.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related