表中的VBA多列引用

你用 ListObjects("TableName") 参考整张表。然后 ListColumns("ColumnHeading") 引用表中的特定列,其中“ColumnHeading”是表中的实际标题名称(或 ListColumns(#) 哪里 # DataBodyRange 以引用列数据的范围。

而不是 VLookup 最好使用 Match Range.Cells 为了得到结果。

像这样的

Sub Demo()
    Dim lo As ListObject
    Dim SearchRange As Range
    Dim LookupRange As Range
    Dim SearchTerm As Variant
    Dim LookupItem As Variant
    Dim idx As Variant

    Set lo = Worksheets("Tables").ListObjects("Table1")
    Set SearchRange = lo.ListColumns("Column2").DataBodyRange
    Set LookupRange = lo.ListColumns("Column8").DataBodyRange

    SearchTerm = "YourSearchTerm"
    idx = Application.Match(SearchTerm, SearchRange, 0)
    If Not IsError(idx) Then
        LookupItem = LookupRange.Cells(idx, 1)
        ' use the result as you wish
        Debug.Print LookupItem
    Else
        ' SearchTerm not found.  What now?
    End If

    'For completeness, heres how to reference a range of columns
    Dim VLookupRange As Range
    Set VLookupRange = lo.Parent.Range(lo.ListColumns("Column2").DataBodyRange, lo.ListColumns("Column8").DataBodyRange)
    Debug.Print VLookupRange.Address

End Sub