Procedure fails on some sheets only

[AlexanderBB]

Should return the First and Last Row numbers in column "C" for Yr in Sheet sht

Sub YrRange(Yr, ByRef First, ByRef Last, sht)
    Last = ThisWorkbook.Sheets(sht).Range("C:C").Find(What:=Yr & "*", LookAt:=xlWhole, lookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    First = ThisWorkbook.Sheets(sht).Range("C:C").Find(What:=Yr & "*", LookAt:=xlWhole, lookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
End Sub

It works correctly in some sheets but in one other I get
Run-time error '91':
Object variable or With block variable not set.
The only difference I can see in the sheet not working is it has only columns A-Z showing. And sometimes a message about can't shift object off sheet when Cut/insert row.

 

[DanteAmor]

Run-time error '91':
Object variable or With block variable not set.

This error is because the column is empty or because it didn't find the information.

The best thing is to set the result of the Find method in an object, then you check if the object is not empty, that is, it contains information and you can now obtain the row numbers:

Sub YrRange(Yr, ByRef First, ByRef Last, sht)
  Dim f As Range
  
  With ThisWorkbook.Sheets(sht).Range("C:C")
    Set f = .Find(Yr & "*", , xlValues, xlWhole, xlByRows, xlPrevious)
    If Not f Is Nothing Then
      Last = f.Row
    
      Set f = .Find(Yr & "*", , xlValues, xlWhole, xlByRows, xlNext)
      First = f.Row
    
    Else
      MsgBox "Column Empty"
    End If
  End With
End Sub

Try and comment.

 

[AlexanderBB]

Thanks Dante, it reported "Column Empty" which made me realise in this sheet it's not Col C.
Duh! My apologies.