选择范围时出现 VBA 运行时错误 1004“应用程序定义或对象定义的错误”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17980854/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Runtime Error 1004 "Application-defined or Object-defined error" when Selecting Range
提问by thomas
I am having an issue with a Error 1004 "Application-defined or Object-defined error" when selecting a range.
选择范围时，我遇到了错误 1004“应用程序定义或对象定义错误”的问题。
I am still able to select rows (ie Rows("21:21").select) and to select ranges in other sheets of the same workbook. I do not believe the error is in the code. Maybe its some setting I am unaware of?
我仍然能够选择行（即Rows("21:21").select）并在同一工作簿的其他工作表中选择范围。我不相信错误出在代码中。也许它的一些设置我不知道？
I have used the exact same code many times before but for some reason I cannot make it function in this sub (I have commented where the error occurs)...
我之前多次使用过完全相同的代码，但由于某种原因我无法让它在这个子函数中起作用（我已经评论了错误发生的地方）......
Sub CopySheet1_to_PasteSheet2()
Dim CLastFundRow As Integer
Dim CFirstBlankRow As Integer
'Finds last row of content
Windows("Excel.xlsm").Activate
Sheets("Sheet1").Activate
Range("C21").Select
'>>>Error 1004 "Application-defined or Object-defined error" Occurs
Selection.End(xlDown).Select
CLastFundRow = ActiveCell.Row
'Finds first row without content
CFirstBlankRow = CLastFundRow + 1
'Copy Data
Range("A21:C" & CLastFundRow).Select
Selection.Copy
'Paste Data Values
Sheets("PalTrakExport PortfolioAIdName").Select
Range("A21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Bring back to top of sheet for consistancy
Range("A21").Select
Range("A1").Select
End Sub
I need to get all fancy in my copying as the amount of rows will change frequently. Again, the below code has been used before without error... but not in this instance.
由于行数会经常变化，因此我需要在复制中尽情发挥。同样，之前使用过以下代码没有错误......但不是在这种情况下。
Dim CLastFundRow As Integer
Dim CFirstBlankRow As Integer
'Finds last row of content
Windows("Excel.xlsm").Activate
Sheets("Sheet1").Activate
Range("C21").Select
'>>>Error 1004 "Application-defined or Object-defined error" Occurs
Selection.End(xlDown).Select
CLastFundRow = ActiveCell.Row
'Finds first row without content
CFirstBlankRow = CLastFundRow + 1
回答by Frank H.
Perhaps your code is behind Sheet1, so when you change the focus to Sheet2 the objects cannot be found? If that's the case, simply specifying your target worksheet might help:
也许您的代码在 Sheet1 后面，所以当您将焦点更改为 Sheet2 时，找不到对象？如果是这种情况，只需指定您的目标工作表可能会有所帮助：
Sheets("Sheet1").Range("C21").Select
I'm not very familiar with how Select works because I try to avoid it as much as possible :-). You can define and manipulate ranges without selecting them. Also it's a good idea to be explicit about everything you reference. That way, you don't lose track if you go from one sheet or workbook to another. Try this:
我对 Select 的工作方式不是很熟悉，因为我尽量避免它:-)。您可以在不选择范围的情况下定义和操作范围。此外，明确您所引用的所有内容也是一个好主意。这样，如果您从一张工作表或工作簿转到另一张工作表或工作簿，您就不会迷失方向。尝试这个：
Option Explicit
Sub CopySheet1_to_PasteSheet2()
Dim CLastFundRow As Integer
Dim CFirstBlankRow As Integer
Dim wksSource As Worksheet, wksDest As Worksheet
Dim rngStart As Range, rngSource As Range, rngDest As Range
Set wksSource = ActiveWorkbook.Sheets("Sheet1")
Set wksDest = ActiveWorkbook.Sheets("Sheet2")
'Finds last row of content
CLastFundRow = wksSource.Range("C21").End(xlDown).Row
'Finds first row without content
CFirstBlankRow = CLastFundRow + 1
'Copy Data
Set rngSource = wksSource.Range("A2:C" & CLastFundRow)
'Paste Data Values
Set rngDest = wksDest.Range("A21")
rngSource.Copy
rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Bring back to top of sheet for consistancy
wksDest.Range("A1").Select
End Sub
回答by Kam
It's a bit late but might be helpful for future reference. I had just had the same issue and I think it's because the macro was placed at the worksheet level. Right click on the modules node on the VBA project window, click on "Insert" => "Module", then paste your macro in the new module (make sure you delete the one recorded at the worksheet level).
这有点晚了，但可能对将来的参考有所帮助。我刚刚遇到了同样的问题，我认为这是因为宏被放置在工作表级别。右键单击 VBA 项目窗口上的模块节点，单击“插入”=>“模块”，然后将宏粘贴到新模块中（确保删除在工作表级别记录的宏）。
回答by Emily
The same thing happened to me. In my case most of the worksheet was in protected mode (though the cells relevant to the macro were unlocked). When I disabled the protection on the worksheet, the macro worked fine...it seems VBA doesn't like locked cells even if they are not used by the macro.
这样的事情我也经历过。在我的情况下，大部分工作表都处于保护模式（尽管与宏相关的单元格已解锁）。当我禁用工作表上的保护时，宏工作正常......即使宏不使用锁定的单元格，VBA 似乎也不喜欢锁定的单元格。
回答by Dave
Some operations in Excel are limited by available Memory. If you repeat the same process over and over it could produce a memory overflow and excel will not be able to repeat it anymore. This happened to me while trying to create several sheets in the same workbook.
Excel 中的某些操作受可用内存的限制。如果你一遍又一遍地重复相同的过程，它可能会产生内存溢出，excel 将无法再重复它。我在尝试在同一个工作簿中创建多个工作表时发生了这种情况。
回答by Cees Timmerman
当您以编程方式将大数组字符串设置为 Excel 2003 中的区域时，您可能会收到“运行时错误 1004”错误消息
In Office Excel 2003, when you programmatically set a range value with an array containing a large string, you may receive an error message similar to the following:
在 Office Excel 2003 中，当您使用包含大字符串的数组以编程方式设置范围值时，您可能会收到类似于以下内容的错误消息：
Run-time error '1004'. Application-defined or operation-defined error.
运行时错误“1004”。应用程序定义或操作定义的错误。
This issue may occur if one or more of the cells in an array (range of cells) contain a character string that is set to contain more than 911 characters.
如果数组（单元格范围）中的一个或多个单元格包含设置为包含 911 个以上字符的字符串，则可能会出现此问题。
To work around this issue, edit the script so that no cells in the array contain a character string that holds more than 911 characters.
要变通解决此问题，请编辑脚本，以便数组中的单元格不包含包含超过 911 个字符的字符串。
For example, the following line of code from the example code block below defines a character string that contains 912 characters:
例如，下面示例代码块中的以下代码行定义了一个包含 912 个字符的字符串：
Sub XLTest()
Dim aValues(4)
aValues(0) = "Test1"
aValues(1) = "Test2"
aValues(2) = "Test3"
MsgBox "First the Good range set."
aValues(3) = String(911, 65)
Range("A1:D1").Value = aValues
MsgBox "Now the bad range set."
aValues(3) = String(912, 66)
Range("A2:D2").Value = aValues
End Sub
Other versions of Excel or free alternativeslike Calcshould work as well.
回答by Zim84
I as well had the same problem and nearly drove crazy. The solution was pretty unexpected.
我也有同样的问题，几乎要发疯了。解决方案出乎意料。
My Excel is shipped out by default that I enter formulas in an Excel-Cell as followed:
我的 Excel 默认发货，我在 Excel 单元格中输入公式，如下所示：
=COUNTIF(Range; Searchvalue)
=COUNTIF(A1:A10; 7) 'Example
Please note, that parameters are separated with a semicolon;. Now if you paste exactly that string into a formula within VBA, for example like:
请注意，参数用分号分隔;。现在，如果您将该字符串完全粘贴到 VBA 中的公式中，例如：
Range("C7").FormulaArray = "=COUNTIF(A1:A10; 7)" 'this will not work
You will get this 1004-error with absolutely no explanation. I spent hours to debug this.. All you have to do is replace all semicolon with commas.
你会得到这个 1004 错误，完全没有解释。我花了几个小时来调试这个。你所要做的就是用逗号替换所有的分号。
Range("C7").FormulaArray = "=COUNTIF(A1:A10, 7)" 'this works
回答by Rolf
I could remove the error (Run-time error '1004'. Application-defined or operation-defined error) by defining the counters as Single
我可以通过将计数器定义为 Single 来消除错误（运行时错误“1004”。应用程序定义或操作定义的错误）
回答by WannabeProger
I had a similar issue, but it turns out I was just referencing a cell which was off the page {i.e. cells(i,1).cut cells (i-1,2)}
我有一个类似的问题，但事实证明我只是引用了一个不在页面上的单元格 {ie cells(i,1).cut cells (i-1,2)}
回答by Alex Merlano
You have to go to the sheet of db to get the first blank row, you could try this method.
你必须去数据库表才能得到第一个空白行，你可以试试这个方法。
Sub DesdeColombia ()
Dim LastRowFull As Long
'Here we will define the first blank row in the column number 1 of sheet number 1:
LastRowFull = Sheet1.Cells(Rows.Count,1).End(xlUp).Offset(1,0).Row
'Now we are going to insert information
Sheet1.Cells(LastRowFull, 1).Value = "We got it"
End Sub
回答by Fabian
I had a similar problem & fixed it applying these steps:
我遇到了类似的问题并应用以下步骤修复了它：
- Unprotecting the sheet that I want to edit
- Changing the range that I had selected by every single cell in the range (exploded)
- 取消保护我要编辑的工作表
- 更改我在范围内的每个单元格中选择的范围（爆炸）
I hope this will help someone.
我希望这会帮助某人。
