vba 选择工作表类的方法失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24872857/
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
Select Method of Worksheet Class Failed
提问by AxxieD
I have this sub in Excel 2010 which is supposed to filter through all the cells in a sheet until it finds a match to Proj No, then paste a field from this row into another field.
我在 Excel 2010 中有这个子程序，它应该过滤工作表中的所有单元格，直到找到与 Proj No 的匹配项，然后将该行中的一个字段粘贴到另一个字段中。
When I try to run the sub, it gives me an error 1004: Select Method of Worksheet Class Failed. I've marked the line where this occurs. Any assistance would be greatly appreciated.
当我尝试运行 sub 时，它给了我一个错误 1004: Select Method of Worksheet Class Failed。我已经标记了发生这种情况的行。任何帮助将不胜感激。
Option Explicit
Private Sub btnNext_Click()
Dim ProjNo As String
Dim Col As String
Dim Row As String
Dim cell As Range
Unload Dialog
formWait.Show
Sheets("Sheet7").Activate
ProjNo = Worksheets("Sheet1").Range("D6").Value
Col = Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In Range("A2:A" & Col)?
If cell.Value = ProjNo Then
Row = Row & cell.Row
End If
Next cell
Workbooks("Form.xlsm").Sheets("Sheet7").Range("Row, 6").Copy Destination:=Sheets("Sheet1").Range("19, 5") ‘Error
Unload formWait
End Sub
采纳答案by Dick Kusleika
I don't know what GWPis, but I think you want to use ProjNothere. The Range property doesn't accept an argument like that. Unless you have a named range of "Row,6" which you don't because it's not a legal name, you have to supply Range with a valid range reference, like A6 or D2:D12, for example.
我不知道是什么GWP，但我想你想在ProjNo那里使用。Range 属性不接受这样的参数。除非您有一个命名范围“Row,6”，因为它不是合法名称，否则您必须为 Range 提供有效的范围引用，例如 A6 或 D2:D12。
Also, you can't concatenate rows and use them in a Range reference to get a larger range. You would have to copy each row inside the loop, union the ranges as you go, or better yet, filter on the value that you want and copy the visible rows.
此外，您不能连接行并在 Range 引用中使用它们来获得更大的范围。您必须复制循环内的每一行，在进行时合并范围，或者更好的是，过滤所需的值并复制可见行。
Try this:
尝试这个：
Private Sub btnNext_Click()
With ThisWorkbook.Worksheets("Sheet7")
'filter for the project id
.Range("A1", .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 6).AutoFilter 1, "=" & .Range("D6").Value
'copy the visible rows
.Range("F2", .Cells(.Rows.Count, 6).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy _
ThisWorkbook.Worksheets("Sheet1").Cells(19, 5)
'get rid of the filter
.AutoFilterMode = False
End With
End Sub
回答by Dan Wagner
There are a few confusing items in your code above, so I wanted to place them long-form here. Let's get started:
上面的代码中有一些令人困惑的项目，所以我想把它们放在这里。让我们开始吧：
Dim Col As String
Dim Row As String
It looks like your design expects these to be of type Longrather than type String. Even if these variables were meant to be strings, I would recommend adjusting their names -- when your fellow developer attempts to review your design, he or she is likely to see names like "Col" or "Row" and think "these are numbers". Easy fix:
看起来您的设计希望这些是 typeLong而不是 type String。即使这些变量是字符串，我还是建议调整它们的名称——当你的开发人员同事试图你的设计时，他或她可能会看到诸如“Col”或“Row”之类的名称并认为“这些是数字”。轻松修复：
Dim Col As Long, Row As Long
The next issue comes up here:
下一个问题出现在这里：
Col = Cells(Rows.Count, "A").End(xlUp).Row
The structure above is a common method for identifying the last ROW, not column. (It also appears that you have switched the "A" and number, which is another easy fix). While it is perfectly acceptable syntactically to name the variable for last row "Col", human users are likely to find this confusing. Identifying the last row (and the last col, which you use in the For Eachloop), as explained in fantastic detail here, would be better handled like this:
上面的结构是识别最后一行而不是列的常用方法。（您似乎还切换了“A”和数字，这是另一个简单的解决方法）。虽然将最后一行的变量命名为“Col”在语法上是完全可以接受的，但人类用户可能会发现这令人困惑。识别最后一行（以及您在For Each循环中使用的最后一个 col ），正如这里详细解释的那样，最好这样处理：
Dim SheetSeven As Worksheet, SheetOne As Worksheet
Dim LastRow As Long, LastCol As Long
Set SheetSeven = ThisWorkbook.Worksheets("Sheet7")
Set SheetOne = ThisWorkbook.Worksheets("Sheet1")
With SheetSeven
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
LastCol = .Range("A" & .Columns.Count).End(xlToLeft).Column
End With
This should make your For Eachloop look like this:
这应该让你的For Each循环看起来像这样：
With SheetSeven
For Each cell in .Range("A2:A" & LastCol)
'... do you comparison and row incrementing here
Next cell
End With
Once you've identified your sheet as a variable, the Range.Copyaction should be much easier as well:
将工作表标识为变量后，Range.Copy操作也应该容易得多：
With SheetSeven
.Range(.Cells(Row, 6)).Copy _
Destination:=SheetOne.Range(SheetOne.Cells(19, 5))
End With
回答by GrumpyWithBill
Also one other thing you may wish to check is the status of Application.ScreenUpdating. With the release of Office 2013 and later, a SDI (Single Document Interface) was introduced. If Application.ScreenUpdating is False and the workbook is not active, the implied call to Workbook.Activate will fail. Check the status of ScreenUpdating and set it to True if needed. You can set it back to False after the first Activate call for that workbook is made. See this article: https://support.microsoft.com/en-us/help/3083825/excel-workbook-is-not-activated-when-you-run-a-macro-that-calls-the-wo
您可能希望检查的另一件事是 Application.ScreenUpdating 的状态。随着 Office 2013 及更高版本的发布，引入了 SDI（单文档界面）。如果 Application.ScreenUpdating 为 False 并且工作簿未处于活动状态，则对 Workbook.Activate 的隐含调用将失败。检查 ScreenUpdating 的状态并在需要时将其设置为 True。在对该工作簿进行第一次 Activate 调用后，您可以将其设置回 False。请参阅这篇文章：https: //support.microsoft.com/en-us/help/3083825/excel-workbook-is-not-activated-when-you-run-a-macro-that-calls-the-wo
回答by SUNIL KUMAR
In my case the error came as the sheet was hidden.so I check if I am not working with the hidden sheet. Or you need to unhide the sheet before you try to select or activate sheet.
在我的情况下，错误是因为工作表被隐藏了。所以我检查我是否没有使用隐藏的工作表。或者您需要在尝试选择或激活工作表之前取消隐藏工作表。
For Each sh In ThisWorkbook.Sheets
If Left(sh.Name, 8) <> "Template" Then
sh.Select
sh.Range("A1").Select
End If
Next
