How do I get Excel to search for text in another sheet, incorporating length, and adjust hyperlink a-CodePudding

I'm trying to update hyperlinks in one Excel spreadsheet, using a Find in another sheet (same workbook). The problem occurs on the "j = c.Find(k).Row" line, where I receive the message "Runtime error '91:' Object variable or With block variable not set."

It doesn't give me any issues with the "d = c.Find(m).Row" which looks like it's been set up identically to me, so I don't understand what's breaking and why. I know nothing about error handling in VBA - I've just never had to use it - so maybe there's something wrong with the result of the search?

I've looked everywhere but can't get a solution to this. Apologies if I've missed an answer to this somewhere, and thank you in advance for your help!

Sub HypFix()

Dim k As String
Dim m As String
Dim i As Long
Dim g As String
Dim d As String
Dim j As String
Dim c As Range

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


'CHANGE SHEET NAMES BELOW AS NEEDED

Set c = Sheets("Tables").Range("A1:A15071")
    
For i = 4 To 337
    If Sheets("Contents").Cells(i, "A").Value <> "" Then
        k = Sheets("Contents").Cells(i, "A").Value
        m = Right(Sheets("Contents").Cells(i, "A").Value, 255)
        g = Sheets("Contents").Cells(i, "A").Value
            If Len(Sheets("Contents").Cells(i, "A").Value) > 255 Then
                d = c.Find(m).Row
                Sheets("Contents").Hyperlinks.Add Anchor:=Sheets("Contents").Cells(i, "A"), _
                Address:="", _
                SubAddress:="'Tables'!A" & d, _
                TextToDisplay:=g
            ElseIf Len(Sheets("Contents").Cells(i, "A").Value) <= 255 Then
                j = c.Find(k).Row
                Sheets("Contents").Hyperlinks.Add Anchor:=Sheets("Contents").Cells(i, "A"), _
                Address:="", _
                SubAddress:="'Tables'!A" & j, _
                TextToDisplay:=g
            End If
    End If
Next i
    
'Message Box when tasks are completed
MsgBox "Task Complete!"

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.CalculateFull

End Sub

CodePudding user response：

You should always set a range to the Range.Find(). This allows you to test whether a value was found without throwing an error.

Sub HypFix()
    Dim i As Long
    Dim c As Range
    Dim Target As Range
    Dim What As String
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    'CHANGE SHEET NAMES BELOW AS NEEDED

    Set c = Sheets("Tables").Range("A1:A15071")
    With Sheets("Contents")
        For i = 4 To 337
            What = .Cells(i, "A").Value
            If Len(What) > 0 Then
 
                Set Target = c.Find(What:=What, LookIn:=xlValues)
                
                Rem Test if anything was found
                If Not Target Is Nothing Then
                    Rem Look for the Last 255 characters
                    Set Target = c.Find(What:=Right(What, 255), LookIn:=xlValues)
                End If
    
                
                Rem If something was found link it
                If Not Target Is Nothing Then
                    .Hyperlinks.Add Anchor:=.Cells(i, "A"), Address:="", SubAddress:="'Tables'!A" & Target.Row
                Else
                    Rem Leave yourself a message of what wasn't found
                    Debug.Print What, " in row "; i; "Not Found"
                End If

            End If
        Next i
    End With
    'Message Box when tasks are completed
    MsgBox "Task Complete!"

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.CalculateFull

End Sub

Note: When the TextToDisplay parameter is omitted from .Hyperlinks.Add the Anchor cell's test is displayed.