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 RangeDim lLastRow As LongDim lLastCol As LongDim rngStartCell As RangeSet rngStartCell = Ws.Range(sName)lLastRow = Ws.Cells.Find(What:="*", After:=Ws.Range(rngStartCell), LookIn:=xlFormulas, _Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _MatchCase:=False).RowlLastCol = Ws.Cells.Find(What:="*", After:=Ws.Range(rngStartCell), LookIn:=xlFormulas, _Lookat:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _MatchCase:=False).ColumnSet GetLastNonEmptyCellOnWorkSheet = Ws.Range(Ws.Cells(lLastRow, lLastCol))End FunctionFrom 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?
Best Answer
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)The error message 'method range of object _worksheet failed' typically occurs when there is an issue with the range method being used on the _worksheet object. This error can have multiple causes, including incorrect worksheet name, incorrect range format, or a problem with the worksheet object itself.
One possible cause of this error is that the worksheet name is misspelled or does not exist. Ensure that the correct name is used when referencing the worksheet. Another possible cause is an incorrect range format. Make sure that the range being used is in the correct format, such as 'A1:B10'.
The error can also occur if there is an issue with the worksheet object itself. Check if the worksheet object is properly initialized and assigned. If not, make sure to correctly set the worksheet object before using the range method.
If none of the above solutions work, try closing and reopening the workbook or restarting the application. Sometimes, temporary issues can cause this error. Additionally, check for any recent changes or updates that might have affected the range method or the worksheet object.
When I used blow code, the same error was occurred.
Dim x As IntegerRange(Cells(2, x + 13))changed the code to blow, the error disappeared.
Dim x As IntegerRange(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.ActivateLANGKeywords = keyDataSheet.Range(Cells(2, 1), Cells(LANGKWCount, 1))COLKeywords = keyDataSheet.Range(Cells(3, 2), Cells(COLKWCount, 2))currentWB.Activate