Intermittent "The object invoked has disconnected from its clients" Fully qualified range problem?

[abssorb]

I'm a casual VBA user. Bit of Newbie.

I have a macro which just timestamps a row with a user's name and the date. It's in a workbook with about 10 worksheets. Each of which has a different number of columns, so I have to search for the right columns.
It needs to work on any worksheet.

This works perfectly most of the time, but every now and again it will throw an error.

Error -2147417848 Automation error
The object invoked has disconnected from its clients

Sometimes Excel becomes unresponsive too.

I can't see where the code is calling an object, but I have seen internet wisdom saying that this can be caused by a lack of fully qualified range definition.
But I can't find any way to fully qualify the activesheet when I need it to be sheet agnostic.

Forgive the scruffy inefficient code, cobbled together from some other great examples on here:

Sub Tag_my_update_on_the_row()
'
' Look for the data governance columns and add my details.
'
' Based on https://www.mrexcel.com/board/threads/macro-to-find-column-number-of-a-certain-value.172151/
' https://www.mrexcel.com/board/threads/how-to-search-for-a-string-and-return-its-column-letter.861462/
'
'
'

Dim SeekIt As Variant

Dim MyRange As String
Dim MyRange2 As String
Dim CheckAction As Integer

    SeekIt = "#Data_Governance"                                'set to whatever you want to look for
    myrow = ActiveCell.Row

    With ActiveSheet.Range("A1:Z20")           '<<< set the range you want to look through. We're just looking at header area
         Set c = .Find(SeekIt, , xlValues, xlPart, , , False)
    End With
    
   If Not c Is Nothing Then
    
        If myrow < 4 Then
            MsgBox ("Can't perform this on header rows. Please select the row you amended.")
            Exit Sub
        End If

    
    ' Get results into a cell reference
        ColLetter = Split(c.Address, "$")(1)
    
        MyRange = ColLetter & myrow
    
    
            ' MsgBox ("Your data " & SeekIt & " was found in column " & ColLetter & "(" & c.Address & ") and your row is " & myrow & "Cell is " & MyRange)
        CheckAction = MsgBox("Data Governance column:" & Chr(13) & Chr(10) & "Tag row " & myrow & " with " & Environ("Username") & "?", vbQuestion + vbYesNo + vbDefaultButton2, "Message Box Title")

        If CheckAction = vbYes Then
            ' do it
            Range(MyRange).Value = Environ("Username")
            Range(MyRange).Select
            ActiveCell.Offset(0, 1).Value = Format(Date, "dd-mm-yyyy") 'Cell to right of active cell

        Else
        ' Don't do it - user choice was no
            Exit Sub
        End If

Else
' The search didn't work.
  MsgBox "There are no Data Governance columns on this worksheet."
End If



End Sub

 

[RoryA]

I can't see any issue with that code. Which line actually causes the problem?

There is no need to select the cell in there, and you don't really need to figure out the column letter, but I don't see either of those things as problematic per se.

 

[abssorb]

Thanks, that's reassuring. Because it's intermittent it's unclear which line is causing the problem. I can keep checking.