VBA: Getting run-time 1004: Method 'Range' of object '_Worksheet' failed when using cells
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:
👉 For more insights, check out this resource.
VBA: Getting run-time 1004: Method 'Range' of object '_Worksheet' failed
on the line:
👉 Discover more in this in-depth guide.
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?
3 Answers
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) 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) 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 