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

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)

Related Posts:

  1. What does <> mean?
  2. How to find a value in an excel column by vba code Cells.Find
  3. What is the function of FormulaR1C1?
  4. What exactly is the function of Application.CutCopyMode property in Excel
  5. VBA Excel – Insert row below with same format including borders and frames
  6. Excel VBA: Can’t get a match, error “Unable to get the Match property of the WorksheetFunction class”
  7. How to install Date Picker form control in Excel 2016
  8. How to install Date Picker form control in Excel 2016
  9. How to fill color in a cell in VBA?
  10. Refreshing all the pivot tables in my excel workbook with a macro
  11. What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?
  12. Average in VBA in Excel
  13. VBA Excel “Compile error: Object Required”
  14. Choosing paper size (NOT DEFAULT sizes) in excel vba
  15. “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
  16. Paste Special error 1004 PasteSpecial method of Range class failed
  17. Cannot run the macro… the macro may not be available in this workbook
  18. How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
  19. How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
  20. Object Required Error in excel VBA
  21. Check if a string contains another string
  22. VBScript to open a dialog to select a filepath
  23. Does VBA have Dictionary Structure?
  24. What are .NumberFormat Options In Excel VBA?
  25. Better way to find last used row
  26. VBA, if a string contains a certain letter
  27. Excel VBA Macro: User Defined Type Not Defined
  28. “Sub or Function not defined” when trying to run a VBA script in Outlook
  29. VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
  30. Select method of Range class failed via VBA
  31. Does VBA contain a comment block syntax?
  32. Why does “Paste Method of Worksheet class failed” occasionally occur?
  33. Deleting a file in VBA
  34. How to return a result from a VBA function
  35. How to clear memory to prevent “out of memory error” in excel vba?
  36. Use VBA to Clear Immediate Window?
  37. VBA: Else without If Error
  38. Recommended IDE for VBA
  39. ByRef argument type mismatch in Excel VBA
  40. How to comment and uncomment blocks of code in the Office VBA Editor
  41. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  42. VBA: Convert Text to Number
  43. VBA Error: “Compile error: Expected End Sub”
  44. Scraping data from website using vba
  45. Pass arguments to Constructor in VBA
  46. Error: “Could Not Find Installable ISAM”
  47. Loop without Do error
  48. Row count where data exists
  49. Test or check if sheet exists
  50. How to detect if user select cancel InputBox VBA Excel
  51. macro run-time error ‘9’: subscript out of range
  52. Why does my VBA code throw an “Invalid outside procedure” error?
  53. VBA: Run time error ’91’?
  54. Compare Dates in VBA
  55. ‘Cannot find DAO350.dll’ file error VB6
  56. Excel Solver Password: what is it and where can it be acquired/bought?
  57. Using Excel VBA to run SQL query
  58. Compile error: Next without For || VBA
  59. Is there a way to crack the password on an Excel VBA Project?
  60. MS Excel Scatterplot converts Months to Numbers
  61. Substring in excel
  62. IndexError: too many indices for array
  63. IndexError: too many indices for array
  64. Excel to Google Sheets – Error: Formula parse error
  65. Exception in thread “AWT-EventQueue-0” java.lang.NullPointerException Error
  66. ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine
  67. IndexError: too many indices for array
  68. Swap x and y axis without manually swapping values
  69. Excel: Calculate the frequency of a particular month in a column of date cells
  70. ImportError: Missing required dependencies [‘numpy’]
  71. WindowsError: [Error 193] %1 is not a valid Win32 application in Python
  72. Looking for a clear description of Excel’s .xlsx XML format
  73. Help needed with Median If in Excel
  74. Invalid column count in CSV input on line 1 Error
  75. Invalid column count in CSV input on line 1 Error
  76. ‘int’ object has no attribute ‘__getitem__’
  77. excel IF formula Error, “You’ve entered too many arguments”
  78. Insert picture into Excel cell
  79. Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browser
  80. What is a correct MIME type for .docx, .pptx, etc.?
  81. Is there Infinity in Spreadsheets?
  82. How to fix file format and extension don’t match?
  83. Declaring variables in Excel Cells
  84. TypeError: Cannot read property “0” from undefined
  85. How to loop in excel without VBA or macros?
  86. How can I open an Excel file in Python?
  87. Python error – IDLE’s subprocess didn’t make connection. Either IDLE can’t start or personal firewall software is blocking connection
  88. ValueError: cannot index with vector containing NA / NaN values
  89. ‘int’ object has no attribute ‘__getitem__’
  90. Need more information about Aborted (core dumped)
  91. How can I import an Excel file into SQL Server?
  92. What is correct content-type for excel files?
  93. How do I get countifs to select all non-blank cells in Excel?
  94. lists and arrays in VBA
  95. What causes java.lang.IncompatibleClassChangeError?
  96. How to run a SQL query on an Excel table?
  97. How do I fix “Undefined variable” error in PHP?
  98. Excel – SUMIFS for multiple columns
  99. Workaround for “Formula omits adjacent cells”
  100. ImportError: No module named win32com.client

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)