SubAddress必须以地址的形式传递，而不是单元格的值。而且你必须在目标工作表中查找单元格才能得到地址。
这就是函数getAddressOfCell返回的结果。

Sub addHyperlink()

Dim wsQueries As Worksheet
Set wsQueries = ThisWorkbook.Worksheets("Queries")

Dim rgType As Range
Set rgType = wsQueries.Range("B2:B6")

Dim subAddress As String
Dim reference As Range

For Each reference In rgType.Cells
    subAddress = getAddressOfCell(reference.Offset(, -1), _
        ThisWorkbook.Worksheets(reference.Value).Cells(1, 1).CurrentRegion)
    If subAddress <> vbNullString Then
        wsQueries.Hyperlinks.Add anchor:=reference, Address:="", subAddress:=subAddress ', TextToDisplay:=reference.Value
    End If
Next reference
End Sub

Private Function getAddressOfCell(strFind As String, rgSearchIn As Range) As String
Dim rgFound As Range
With rgSearchIn 
    Set rgFound = .Find(what:=strFind)
    If Not rgFound Is Nothing Then
        getAddressOfCell = rgFound.Address(True, True, , True)
    End If
End With
End Function

添加超链接

Option Explicit

Sub AddHyperlinksToQueries()

    Dim sNames(): sNames = VBA.Array("TB", "RD Schedule")
    Dim sAddresses(): sAddresses = VBA.Array("A2", "A2")
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim dws As Worksheet: Set dws = wb.Sheets("Queries")
    Dim drg As Range
    Set drg = dws.Range("B3", dws.Cells(dws.Rows.Count, "B").End(xlUp))
    
    Dim dCell As Range, sIndex, sName As String, sAddress As String
    
    For Each dCell In drg.Cells
        sName = CStr(dCell.Value)
        sIndex = Application.Match(sName, sNames, 0)
        If IsNumeric(sIndex) Then ' 'sIndex' is one-based...
            sIndex = sIndex - 1 ' ... the arrays are zero-based
            sName = sNames(sIndex)
            sAddress = sAddresses(sIndex)
            dCell.Hyperlinks.Add Anchor:=dCell, Address:="", _
                SubAddress:="'" & sName & "'!" & sAddress, TextToDisplay:=sName
            sAddresses(sIndex) = dws.Range(sAddress).Offset(1).Address(0, 0)
        Else ' not in array
            dCell.Clear
        End If
    Next dCell
        
    MsgBox "Hyperlinks created.", vbInformation
        
End Sub