VBA:获取运行时1004:使用单元格时对象"_Worksheet"的方法"Range"失败 [英] VBA: Getting run-time 1004: Method 'Range' of object '_Worksheet' failed when using cells

问题描述

我有2个工作表:资产"和概述".

I have 2 worksheets: Assets and Overview.

所有功能都放在一个模块中.

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:获取运行时1004:对象"_Worksheet"的方法"Range" 失败

VBA: Getting run-time 1004: Method 'Range' of object '_Worksheet' failed

在线:

 Set GetLastNonEmptyCellOnWorkSheet = Ws.Range(Ws.Cells(lLastRow, lLastCol))

工作表资产上有数据.最后使用的单元格是W9(lLastRow = 9和lLastCol = 23).

There is data on the worksheet Assets. The last used cell is W9 (lLastRow = 9 and lLastCol = 23).

知道为什么这行不通吗?

Any idea why this is not working?

推荐答案

这是您的问题说明:

Set GetLastNonEmptyCellOnWorkSheet = Ws.Range(Ws.Cells(lLastRow, lLastCol))

计算最里面的括号:

ws.Cells(lLastRow, lLastCol)

此是，但范围的默认属性是其.Value.除非存在与该值对应的命名范围，否则会出现错误.

This is a range, but a range's default property is its .Value. Unless there is a named range corresponding to this value, the error is expected.

相反，请尝试:

Set GetLastNonEmptyCellOnWorkSheet = Ws.Range(Ws.Cells(lLastRow, lLastCol).Address)

或者您可以稍微简化一下:

Or you could simplify slightly:

Set GetLastNonEmptyCellOnWorkSheet = Ws.Cells(lLastRow, lLastCol)

这篇关于VBA:获取运行时1004:使用单元格时对象"_Worksheet"的方法"Range"失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！