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

Mylarbi

New Member
Joined
Feb 9, 2020
Messages
48
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
VBA Code:
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
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'.
1664810178222.png

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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top