Random '1004 application-defined or object-defined error'/'1004 PasteSpecial method of Range class failed'

Question

A macro (not mine though, I "inherited" it) runs multiple loops. The code has a couple of thousands lines so I will provide only the wonky part of the loop in the snippet.

Dim repoLastRow As Integer, repoLastCol As Integer
Worksheets("ATT_LEV").Activate
With ActiveSheet
    repoLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
repoLastCol = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column
Cells(repoLastRow + 1, 1).Value = xmlAgreement1
Cells(repoLastRow + 1, 2).Value = repoLastRow + 1
Cells(repoLastRow + 1, 5).Value = pubCurrCNID

Cells(repoLastRow + 1, 4).Formula = "=IF(IFERROR(FIND(""MASTER"",'Import xml 0'!A2,1),0)>0,""MASTER"",IF(IFERROR(FIND(""ANNEX"",'Import xml 0'!A2,1),0)>0,""ANNEX"",""""))"
Cells(repoLastRow + 1, 4).Value = Cells(repoLastRow + 1, 4).Value

Range(Cells(repoLastRow + 1, 3), Cells(repoLastRow + 1, repoLastCol)).Dirty
For i = 5 To repoLastCol
    column_letter = Split(Columns(i + 1).Address, ":")(0)
    Cells(repoLastRow + 1, i + 1).Formula = "=IFERROR(IF(VLOOKUP(" & column_letter & "1&" & column_letter & "2,CompareSingle!$A:$I,9,FALSE)=0,"""",VLOOKUP(" & column_letter & "1&" & column_letter & "2,CompareSingle!$A:$I,8,FALSE)),"""")"
    Cells(repoLastRow + 1, i + 2).Formula = "=IFERROR(IF(VLOOKUP(" & column_letter & "1&" & column_letter & "2,CompareSingle!$A:$I,9,FALSE)=0,"""",VLOOKUP(" & column_letter & "1&" & column_letter & "2,CompareSingle!$A:$I,9,FALSE)),"""")"
    i = i + 1
Next i
Range(Cells(repoLastRow + 1, 3), Cells(repoLastRow + 1, 3)).Formula = "=CompareSingle!C1"

Range(Cells(repoLastRow + 1, 3), Cells(repoLastRow + 1, repoLastCol)).Value = _
Range(Cells(repoLastRow + 1, 3), Cells(repoLastRow + 1, repoLastCol)).Value

This is the specific part that crashes.

Range(Cells(repoLastRow + 1, 3), Cells(repoLastRow + 1, repoLastCol)).Value = _
Range(Cells(repoLastRow + 1, 3), Cells(repoLastRow + 1, repoLastCol)).Value

The thing is that this specific line gives me the '1004 application-defined or object-defined error' but only after a couple of iterations of the loop, let's say after 40. So for the 41st time macro goes through this part of the code, it simply breaks. Sometimes it is almost 50 times it loops flawlessly, but ultimately it will always crash - and I've never made more than 50 loops. Sometimes it ends up with a complete Excel freeze, but more often it's just a debugger pop-up. Funny thing is that if I stop the macro and start it from the loop at which it crashed, it will go through this statement smoothly and will break again after another couple dozen passages. The funniest thing is, however, it always breaks on this line only in the whole macro although a similar pattern (.value=.value) is successfully applied in other parts of the macro (similar range, similar sheets, similar type of data).

I thought a workaround for the buggy part would be the following:

Range(Cells(repoLastRow + 1, 3), Cells(repoLastRow + 1, repoLastCol)).Copy
Range(Cells(repoLastRow + 1, 3), Cells(repoLastRow + 1, 3)).PasteSpecial xlPasteValues
Application.CutCopyMode = False

but instead I'm receiving '1004 PasteSpecial method of Range class failed' (after some time of course, initially it works OK too). Also tried .Value2=.Value2, but same crap.

I mentioned that after stopping the macro and running it anew from the last correct loop it goes OK. That's true, but after the crash on this line Excel usually becomes sort of unresponsive to VBA calls and I'm only able to proceed after I save, quit and reopen the worksheet. For example,Worksheet.Activate method has no effect (nothing happens, the called worksheet doesn't get activated) or Cells.Clear doesn't work either and renders an error. After reopening the workbook all is back to normal and I can run the procedure. The macro is stored in an .xlsb if that matters, was created and run in Excel 2010 (initially in .xlsm).

Anyone might have an idea why this keeps happening? And why at random?

PS. I realize the code might not be optimized (e.g. you might pick on the Worsksheet.Activate method used) but, again, this is sth I've been given to work with and I'd rather not rewrite the code, at least for now, unless necessary to make it work.

EDIT

I have solved my problem, at least partially. What I did is convert the cell to values immediately after it has been populated with formula and now I can loop forever:

  For i = 5 To repoLastCol
        column_letter = Split(Columns(i + 1).Address, ":")(0)
        Cells(repoLastRow + 1, i + 1).Formula = "=IFERROR(IF(VLOOKUP(" & column_letter & "1&" & column_letter & "2,CompareSingle!$A:$I,9,FALSE)=0,"""",VLOOKUP(" & column_letter & "1&" & column_letter & "2,CompareSingle!$A:$I,8,FALSE)),"""")"
        Cells(repoLastRow + 1, i + 1).Value = Cells(repoLastRow + 1, i + 1).Value
        Cells(repoLastRow + 1, i + 2).Formula = "=IFERROR(IF(VLOOKUP(" & column_letter & "1&" & column_letter & "2,CompareSingle!$A:$I,9,FALSE)=0,"""",VLOOKUP(" & column_letter & "1&" & column_letter & "2,CompareSingle!$A:$I,9,FALSE)),"""")"
        Cells(repoLastRow + 1, i + 2).Value = Cells(repoLastRow + 1, i + 2).Value
        i = i + 1
    Next i

I'm not entirely satisfied with this workaround because it still doesn't explain what's causing the error. You might find it interesting but over the last couple of runs the error happened exactly at 40th loop (and of course always at the same line). When excel crashed(on some occasions) I tried to debug it with Visual Studio and once got info that problem is with VBE7.dll.

Any guesses as to the nature of the problem?

You might notice now that coded sections do not show bold. Which line produces the error? — user4039065
the editor marks it with **. The line I meant is : Range(Cells(repoLastRow + 1, 3), Cells(repoLastRow + 1, repoLastCol)).Value = _ Range(Cells(repoLastRow + 1, 3), Cells(repoLastRow + 1, repoLastCol)).Value — papa_dance
related topic: Are you returning a string or a number with that VLOOKUP(" & column_letter & "1&" & column_letter & "2,...? If you are expecting a string and don't want a zero when you hit a blank there is a better way but it is not recommended if you are expecting a number returned. — user4039065
If you go to the ATT_LEV* worksheet name tab and right-click ► View Code, does the worksheet's code sheet have any event macros; particularly a Worksheet_Change? Does ThisWorkbook have any event macros; particularly a Workbook_SheetChange? — user4039065

Unknown Unknown · Accepted Answer · 2015-11-06T11:47:58

You are referencing the parent worksheet correctly with,

With ActiveSheet
    repoLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Why stop there? The With ... End With statement is your biggest friend for assigning (and maintaining) the parent worksheet.

Row number and column number assignments should always be Long, not Integer no matter how much 'cooler' Integer sounds.

Dim repoLastRow As Long, repoLastCol As Long

With Worksheets("ATT_LEV")
    .Activate
    repoLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    repoLastCol = .Cells(3, Columns.Count).End(xlToLeft).Column

    .Cells(repoLastRow + 1, 1).Value = xmlAgreement1
    .Cells(repoLastRow + 1, 2).Value = repoLastRow + 1
    .Cells(repoLastRow + 1, 5).Value = pubCurrCNID

    .Cells(repoLastRow + 1, 4).Formula = "=IF(IFERROR(FIND(""MASTER"",'Import xml 0'!A2,1),0)>0,""MASTER"",IF(IFERROR(FIND(""ANNEX"",'Import xml 0'!A2,1),0)>0,""ANNEX"",""""))"
    .Cells(repoLastRow + 1, 4).Value = Cells(repoLastRow + 1, 4).Value

    .Range(.Cells(repoLastRow + 1, 3), .Cells(repoLastRow + 1, repoLastCol)).Dirty
    For i = 5 To repoLastCol
        column_letter = Split(.Columns(i + 1).Address, ":")(0)
        .Cells(repoLastRow + 1, i + 1).Formula = "=IFERROR(IF(VLOOKUP(" & column_letter & "1&" & column_letter & "2,CompareSingle!$A:$I,9,FALSE)=0,"""",VLOOKUP(" & column_letter & "1&" & column_letter & "2,CompareSingle!$A:$I,8,FALSE)),"""")"
        .Cells(repoLastRow + 1, i + 2).Formula = "=IFERROR(IF(VLOOKUP(" & column_letter & "1&" & column_letter & "2,CompareSingle!$A:$I,9,FALSE)=0,"""",VLOOKUP(" & column_letter & "1&" & column_letter & "2,CompareSingle!$A:$I,9,FALSE)),"""")"
        i = i + 1
    Next i
    .Range(.Cells(repoLastRow + 1, 3), .Cells(repoLastRow + 1, 3)).Formula = "=CompareSingle!C1"

    .Range(.Cells(repoLastRow + 1, 3), .Cells(repoLastRow + 1, repoLastCol)) = _
        .Range(.Cells(repoLastRow + 1, 3), .Cells(repoLastRow + 1, repoLastCol)).Value
End With

Random '1004 application-defined or object-defined error'/'1004 PasteSpecial method of Range class failed'

1 Answers