CODEWITHSUNDEEP
vbaexcelcycle
Seya
Seya

Reputation: 91

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

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 :)

Upvotes: 0

Views: 2520

Answers (1)

user2140261
user2140261

Reputation: 7993

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

Upvotes: 0

Related Questions