动态超链接可vlookup可vlookup一个单元格值，并找到从数组到输入到URL的相应​​ID

Question

在这种情况下，我想vlookup vookup value，并将其与列r：r中的ID匹配。 Q列：Q是可以找到单元格的地方。

单元格值可用并提取信息。我不能把它带到vlookup。

Public Sub Login_To_Hyperlink()
    Dim VBAIDPull As Range
    Dim Cell As Range
    Dim ID As String
    
    Set VBAIDPull = Workbooks("testupdated.xlsm").Sheets("Overview").Range("Q2:Q250,R2:R250")

    ID = Application.WorksheetFunction.VLookup(Cell.Value, VBAIDPull, 2, False)

    For Each Cell In Range("A2:A250")
        If Cell <> "" Then
            ActiveSheet.Hyperlinks.Add Anchor:=Cell, _
            Address:=Cell.Value, _
            ScreenTip:=ID, _
            TextToDisplay:=Cell.Value
        End If
    Next
End Sub

Answer 1

这可能是您想要的：

Public Sub Login_To_Hyperlink()
    Dim VBAIDPull As Range, ws As Worksheet
    Dim Cell As Range, ID As Variant 'not string
    
    'you can use ThisWorkbook.Sheets() if your table's in the same workbook as your code
    Set VBAIDPull = Workbooks("testupdated.xlsm").Sheets("Overview").Range("Q2:R250")
    Set ws = ActiveSheet
    
    For Each Cell In ws.Range("A2:A250").Cells
        If Cell.Value <> "" Then
            ID = Application.VLookup(Cell.Value, VBAIDPull, 2, False)
            If Not IsError(ID) Then 'got a match?
                ws.Hyperlinks.Add Anchor:=Cell, _
                    Address:=Cell.Value, _
                    ScreenTip:=ID, _
                    TextToDisplay:=Cell.Value
            End If 'got a match
        End If     'cell has value
    Next Cell
End Sub