如何讓Excel在另一個作業表中搜索文本、合并長度并相應地調整超鏈接？

我正在嘗試使用在另一張作業表（同一作業簿）中查找來更新一個 Excel 電子表格中的超鏈接。問題出現在“j = c.Find(k).Row”行，我收到訊息“運行時錯誤'91：'物件變數或未設定塊變數。”

“d = c.Find(m).Row”沒有給我任何問題，看起來它的設定與我相同，所以我不明白什么是破壞以及為什么。我對 VBA 中的錯誤處理一無所知 - 我從來沒有使用過它 - 所以搜索結果可能有問題？

我到處尋找，但無法解決這個問題。如果我在某個地方錯過了這個問題的答案，我們深表歉意，并提前感謝您的幫助！

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

uj5u.com熱心網友回復：

您應該始終將范圍設定為Range.Find(). 這允許您在不引發錯誤的情況下測驗是否找到了值。

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

注意：當引數從Anchor 單元格的測驗中TextToDisplay省略時，將顯示。.Hyperlinks.Add

標籤：擅长 vba 超链接

上一篇：在兩組之間尋找唯一性

下一篇：如果在單元格上找到特定值，則洗掉范圍內的所有單元格-VBA