Run-time error ‘91’: Object variable or With block variable not set

[Mylarbi]

Hi, I am a novice in VBA. However, I have managed to compile this code. It is used to create a cell note with a few data from a webpage copied into clipboard.
The code works when the Excel file is freshly opened. However, if I do anything else and I try to use the macro along the way, I get the error [Run-time error ‘91’: Object variable or With block variable not set].
The error is always on the line on the line "s1 = Replace.....". I guess the problem is got to do with a basic VBA code practice but I can't figure it out.
Please any help to avoid this error or bug will be much appreciated.

Sub ClipboardToNote()
    Dim r  As Range
    Dim rvDat  As Range
    Dim tt  As Range
    Dim wt  As Range
    Dim C As Comment
    Dim CText As String
    Dim s As String
    Dim s0 As String
    Dim s1 As String
    Dim s2 As String
    Dim sHop As Worksheet
    Dim sTcka As Worksheet
'
    Set r = ActiveCell
    Set sHop = Sheets("Hop") 'SHEET IS ONLY USED AS A TEMPORARY PASTE FROM CLIPBOARD BEFORE PICKING RELEVANT DATA FOR THE NOTE'
    Set rvDat = sHop.Range("A1:A200")
    Set sTcka = Sheets("Ticker")
   
    Application.ScreenUpdating = False
    sHop.Activate
    rvDat.Clear
    Range("A1").Select
    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
'
    With rvDat
    Set tt = rvDat.Find("Total applications", LookIn:=xlValues)
    s1 = Replace(tt, "Total applications", " total") ''INTERMITENT ERROR ON THIS LINE
    Set wt = rvDat.Find("Applications awaiting response", LookIn:=xlValues)
    s2 = Replace(wt, "Applications awaiting response", " awaiting")
    Set cp = rvDat.Find("Applications accepted", LookIn:=xlValues)
    End With
'
    sTcka.Activate
    s = ""
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        On Error Resume Next
        .GetFromClipboard
        s = .GetText
        On Error GoTo 0
    End With

    If Trim(s) <> "" Then
        On Error Resume Next
        Set C = r.AddComment
        On Error GoTo 0

        If C Is Nothing Then                          'already has a comment
            Set C = r.Comment
            CText = C.Text & vbCrLf & "--------" & vbCrLf & s1 & vbCrLf & s2 & vbCrLf & "[" & Format(VBA.Now, "DD/MMM/YY hh:mm") & "]"
            C.Text CText
            C.Shape.TextFrame.AutoSize = True
        Else                                          'make new comment
            CText = s1 & vbCrLf & s2 & vbCrLf & "[" & Format(VBA.Now, "DD/MMM/YY hh:mm") & "]"
            C.Text CText
            C.Shape.TextFrame.AutoSize = True
        End If
    End If
End Sub

 

[Joe4]

That usually means it isn't finding what you are looking for in your range.
So it doesn't appear to be "Total assignments" anywhere in A1:A200.
Note that the way you have written your code, it needs to be an exact perfect match.

 

[Mylarbi]

That usually means it isn't finding what you are looking for in your range.
So it doesn't appear to be "Total assignments" anywhere in A1:A200.
Note that the way you have written your code, it needs to be an exact perfect match.

Hi Joe4, thanks for helping. Sorry I updated the word 'assignments' to 'applications'.

This is how the info appears when the macro pastes on the Hop sheet.
It works when the macro is the only thing I am doing since I opened the Excel file.
Why won't it work again moments later if I have done something else, like typing in another Excel file before returning to run the macro?

 

[Joe4]

Try putting a break-point in your code, and stepping through one line at a time, until you get to that error.
Make sure that you are in the right workbook and the data exists when that step is hit.