Method Cells object -Worksheet failed

Question

I have a function, which should be run very often:

 Public Function FindNumberofhandle(stsmenthandle As String) As Long
 Dim r As Long
 Dim LastUsedRow As Long
 LastUsedRow = Worksheets(2).Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
 For i = 1 To LastUsedRow
     If ActiveWorkbook.Sheets(2).Cells(i, 2).Value = stsmenthandle Then 'ERROR HERE
         r = i
     End If
 Next i
 FindNumberofhandle = r
 End Function

I am getting this error:

 run time error -2147417748(80010108)
 Method Cells of Object Worksheet failed

what should I do to solve this problem?

Answer 1

I think you need to replace

ActiveWorkbook.Worksheets(2).Cells(i, 2).Value = stsmenthandle

with

ActiveWorkbook.Worksheets(2).Cells(i, 2).Value = stsmenthandle

Sheets is a collection of Charts Sheets and Worksheets so if a chart sheet is getting picked up when you reference sheets(2) then the property .cells will not work.

Let us know if this works, I am curious as well.

Ignoring cells with error values:

If Not iserror(ActiveWorkbook.Worksheets(2).Cells(i, 2)) then
     If ActiveWorkbook.Worksheets(2).Cells(i, 2).Value = stsmenthandle Then
         r = i
     End If
End if