VBA Run-time error '-2147417848 (80010108'): But I can't find what's wrong.

[flanna]

I use this VBA in a macro. Excel2016.:

Option Explicit​

​

Sub LastRowWithData()​

Dim lastRow As Long​

lastRow = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row​

Range("Control!B2") = lastRow​

Range("J1") = lastRow​

End Sub​

The debugger indicates that the red highlighted row is the problem. I can't find what is wrong with this line. The ActiveSheet has over 100 rows filled with data, including column E.

Can anyone help?

 

[oldbrewer]

you are missing a close bracket at the end maybe because Range(whatever)

 

[flanna]

Nearly forgot... The second line of the error code is: Method 'Range' of object '_Worksheet' failed

 

[flanna]

Thanks, but I don't see any bracket that has not been closed.??..

 

[Andrew Poulsom]

I can't reproduce the error in Excel 2016, but does this work?

lastRow = ActiveSheet.Range("E" & ActiveSheet.Rows.Count).End(xlUp).Row

 

[flanna]

I can't reproduce the error in Excel 2016, but does this work?

lastRow = ActiveSheet.Range("E" & ActiveSheet.Rows.Count).End(xlUp).Row

Thanks, but the problem remains, no difference.

What's annoying is that the code was running for days before this error occurred. The code runs on 2 independent computers, neither have had any updates recently.

 

[flanna]

Option Explicit
Sub LastRowWithData()
Dim sht As Worksheet
Dim lastRow As Long
Set sht = ThisWorkbook.Worksheets("Sales")
lastRow = sht.Cells(sht.Rows.Count, "E").End(xlUp).Row
Range("Control!B2").Value = lastRow
End Sub

Had to experiment to find a solution. Seems that there were multiple issues with my original code.
1. Selecting last row using Active.Sheet replaced with named sheet with Set sht = ThisWorkbook.Worksheets("Sales")
2. Range("Control!B2") has .Value added
3. Range("J1") removed. Writing back to originating sheet causes multiple errors.

Anyone got ideas why these changes work? Could I improve them?