VBA：获取运行时 1004：使用单元格时对象“_Worksheet”的方法“范围”失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27802286/
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: Getting run-time 1004: Method 'Range' of object '_Worksheet' failed when using cells
提问by user1856844
I have 2 worksheets: Assets and Overview.
我有 2 个工作表：资产和概览。
The functions are all put in a module.
所有的功能都放在一个模块中。
Public Function GetLastNonEmptyCellOnWorkSheet(Ws As Worksheet, Optional sName As String = "A1") As Range
Dim lLastRow As Long
Dim lLastCol As Long
Dim rngStartCell As Range
Set rngStartCell = Ws.Range(sName)
lLastRow = Ws.Cells.Find(What:="*", After:=Ws.Range(rngStartCell), LookIn:=xlFormulas, _
Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False).Row
lLastCol = Ws.Cells.Find(What:="*", After:=Ws.Range(rngStartCell), LookIn:=xlFormulas, _
Lookat:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Set GetLastNonEmptyCellOnWorkSheet = Ws.Range(Ws.Cells(lLastRow, lLastCol))
End Function
From the worksheet Overview I call:
从我调用的工作表概览中：
Set RngAssets = GetLastNonEmptyCellOnWorkSheet(Worksheets("Assets"), "A1")
But I always get the error:
但我总是收到错误：
VBA: Getting run-time 1004: Method 'Range' of object '_Worksheet' failed
VBA：获取运行时 1004：对象“_Worksheet”的方法“范围”失败
on the line:
在线上：
Set GetLastNonEmptyCellOnWorkSheet = Ws.Range(Ws.Cells(lLastRow, lLastCol))
There is data on the worksheet Assets. The last used cell is W9 (lLastRow = 9 and lLastCol = 23).
工作表资产上有数据。最后使用的单元格是 W9（lLastRow = 9 和 lLastCol = 23）。
Any idea why this is not working?
知道为什么这不起作用吗？
回答by David Zemens
Here is your problem statement:
这是您的问题陈述：
Set GetLastNonEmptyCellOnWorkSheet = Ws.Range(Ws.Cells(lLastRow, lLastCol))
Evaluate the innermost parentheses:
评估最里面的括号：
ws.Cells(lLastRow, lLastCol)
This isa range, but a range's default property is its .Value. Unless there is a named range corresponding to this value, the error is expected.
这是一个范围，但范围的默认属性是它的.Value. 除非存在与此值对应的命名范围，否则会出现错误。
Instead, try:
相反，请尝试：
Set GetLastNonEmptyCellOnWorkSheet = Ws.Range(Ws.Cells(lLastRow, lLastCol).Address)
Or you could simplify slightly:
或者你可以稍微简化一下：
Set GetLastNonEmptyCellOnWorkSheet = Ws.Cells(lLastRow, lLastCol)
回答by bluetata
When I used blow code, the same error was occurred.
当我使用吹码时，发生了同样的错误。
Dim x As Integer
Range(Cells(2, x + 13))
changed the code to blow, the error disappeared.
将代码更改为blow，错误消失了。
Dim x As Integer
Range(Cells(2, x + 13).Address)
回答by Yun Jia Fei
I found when the range I wanted to use not in the active sheet, then the error occurred. Active the sheet before you want to use range method in it, that fixed my issue.
我发现当我想使用的范围不在活动表中时，就会发生错误。在您想在其中使用范围方法之前激活工作表，这解决了我的问题。
Here is my code: I wanted to assign a range to an array.
这是我的代码：我想为数组分配一个范围。
keyDataSheet.Activate
LANGKeywords = keyDataSheet.Range(Cells(2, 1), Cells(LANGKWCount, 1))
COLKeywords = keyDataSheet.Range(Cells(3, 2), Cells(COLKWCount, 2))
currentWB.Activate
