随机"1004应用程序定义或对象定义的错误"/"1004 Range类的PasteSpecial方法失败" [英] Random '1004 application-defined or object-defined error'/'1004 PasteSpecial method of Range class failed'

问题描述

一个宏(虽然不是我的，我继承"了它)运行多个循环.该代码有成千上万的行，因此我仅在代码片段中提供循环的不稳定部分.

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

这是崩溃的特定部分.

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

问题是，这行代码给了我"1004应用程序定义的错误或对象定义的错误"，但仅在循环的几次迭代之后，比如说在40之后.所以在第41次宏通过了这一部分的代码，它只是打破.有时它几乎是完美循环的50倍，但最终它总是会崩溃-而且我从来没有做超过50次循环.有时，它最终会完全冻结Excel，但更多情况下，它只是调试器弹出窗口.有趣的是，如果我停止宏并从崩溃的循环中启动它，它将顺利通过该语句，并在另外几十个段落之后再次中断.最有趣的是，尽管相似的模式( .value = .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

但是我却收到了"Range类的1004 PasteSpecial方法失败"(当然，经过一段时间后，最初它也可以正常工作).还尝试了 .Value2 = .Value2 ，但效果不佳.

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.

我提到停止宏并从最后一个正确的循环重新运行它之后，它运行正常.没错，但是在此行崩溃后，Excel通常变得对VBA调用无响应，并且只有在保存，退出并重新打开工作表后，我才能继续进行操作.例如， Worksheet.Activate 方法不起作用(什么也没有发生，被调用的工作表不会被激活)或 Cells.Clear 也不起作用并产生错误..重新打开工作簿后，一切恢复正常，我可以运行该过程.如果需要的话，宏将存储在 .xlsb 中，并在Excel 2010中创建并运行(最初在 .xlsm 中).

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.我意识到代码可能未经过优化(例如，您可能会选择使用的 Worsksheet.Activate 方法)，但是，这又是我可以使用的，我宁愿不重写至少直到现在为止，除非有必要使其正常工作.

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.

编辑

我至少部分地解决了我的问题.我所做的是在用公式填充单元格后立即将其转换为值，现在我可以永远循环:

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

我对这种解决方法并不完全满意，因为它仍然无法解释导致错误的原因.您可能会发现它很有趣，但是在最近的几次运行中，错误完全在第40个循环上发生了(完全)(当然，总是在同一行).当excel崩溃(在某些情况下)时，我尝试使用Visual Studio对其进行调试，一旦获得信息，该问题就是VBE7.dll.

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 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.

行号和列号分配应始终为 Long ，而不是 Integer ，无论多少更酷"的Integer声音.

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

这篇关于随机"1004应用程序定义或对象定义的错误"/"1004 Range类的PasteSpecial方法失败"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！