作者:Blackage | 来源:互联网 | 2023-05-28 10:23
我有2个工作表:资产和概述.
这些功能都放在一个模块中.
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
从工作表概述我打电话:
Set RngAssets = GetLastNonEmptyCellOnWorkSheet(Worksheets("Assets"), "A1")
但我总是得到错误:
VBA:获取运行时1004:对象'_Worksheet'的方法'范围'失败
在线上:
Set GetLastNOnEmptyCellOnWorkSheet= Ws.Range(Ws.Cells(lLastRow, lLastCol))
工作表资产上有数据.最后使用的单元格是W9(lLastRow = 9,lLastCol = 23).
知道为什么这不起作用吗?
1> David Zemens..:
这是你的问题陈述:
Set GetLastNOnEmptyCellOnWorkSheet= Ws.Range(Ws.Cells(lLastRow, lLastCol))
评估最里面的括号:
ws.Cells(lLastRow, lLastCol)
这是一个范围,但范围的默认属性是它.Value
.除非存在与此值对应的命名范围,否则将出现错误.
相反,尝试:
Set GetLastNOnEmptyCellOnWorkSheet= Ws.Range(Ws.Cells(lLastRow, lLastCol).Address)
或者您可以略微简化:
Set GetLastNOnEmptyCellOnWorkSheet= Ws.Cells(lLastRow, lLastCol)