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

Question

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: Getting run-time 1004: Method 'Range' of object '_Worksheet' failed

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).

Any idea why this is not working?

Answer 1

Here is your problem statement:

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

Evaluate the innermost parentheses:

ws.Cells(lLastRow, lLastCol)

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.

Instead, try:

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

Or you could simplify slightly:

Set GetLastNonEmptyCellOnWorkSheet = Ws.Cells(lLastRow, lLastCol)

Answer 2

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.

Dim x As Integer
Range(Cells(2, x + 13).Address)