excel - 超链接 Excel 中的整个列，直到文本“总计”

Question

我正在尝试根据该单元格的内容对整个“B”列进行超链接，直到它到达 A 列中的文本“Total”。我正在使用下面的代码，我尝试了 If 条件和 Do 循环，但是我没有得到结果。

顺便提一下，每行后面都有一个空白行，它可以是动态的，因为它基于数据。

任何帮助表示赞赏。

Private Sub Hyperlink_1()

Dim i
Dim file As String
Dim path As String
Dim file1

i = 6

While Range("B" & i).Value + "" <> ""

path = Range("C3").Value & Application.PathSeparator
file = Dir$(path & Range("B" & i).Value & ".*")
file1 = path & file

ActiveSheet.Hyperlinks.Add _
Anchor:=Range("B" & i).Offset(, 0), _
Address:=file1
Range("B" & i).Offset(, 0).Font.Underline = xlUnderlineStyleNone
Range("B" & i).Offset(, 0).Font.Color = -10477568

i = i + 1

Wend

End Sub

标签： excelvbahyperlink

Answer 1

尝试

Private Sub Hyperlink_1()

Dim I
Dim File As String
Dim Path As String
Dim File1
Dim rTot As Range

    Set rTot = Range("A:A").Find("Total")
    If Not rTot Is Nothing Then
        Path = Range("C3") & Application.PathSeparator
        For I = 6 To rTot.Row - 1
            If Range("B" & I) <> "" Then
                File = Dir$(Path & Range("B" & I) & ".*")
                If File <> "" Then
                    File1 = Path & File
                    ActiveSheet.Hyperlinks.Add _
                        Anchor:=Range("B" & I), _
                        Address:=File1
                        Range("B" & I).Font.Underline = xlUnderlineStyleNone
                        Range("B" & I).Font.Color = -10477568
                End If
            End If
        Next I
    Else
        MsgBox "Total not found", vbCritical, "CRITICAL"
    End If

End Sub