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

excel – 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 ranges 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)

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

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

Leave a Reply

Your email address will not be published.