excel - VBA VLookup 按列名

Question

我正在尝试执行 VLookup，其中我将提取的数据列可能会有所不同。如何搜索列名而不必提供固定的列号？ Application.VLookup(.Cells(row, 1).value, Worksheets("Sheet1").Range("A2:Z" & lastRow_Sheet1), 4, 0)Application.VLookup(.Cells(row, 1).value, Worksheets("Sheet1").Range("A2:Z" & lastRow_Sheet1), COLUMNNAME, 0) 我试图让 MATCH 函数在这里工作，但没有运气:Application.VLookup(.Cells(row, 1).value, Worksheets("Sheet1").Range("A2:Z" & lastRow_Sheet1), WorksheetFunction.Match("Column Name", "A1:Z1", 0), 0)编辑:解决方案在这里Application.VLookup(.Cells(row, 1).value, Worksheets("Sheet1").Range("A2:Z" & lastRow_Sheet1), WorksheetFunction.Match("Column Name", Range("A1:Z1"), 0), 0)

Answer 1

Match而不是 VLookup您可以使用以下函数来获取列号:

Function getTableColumnNumber( _
    ByVal HeaderRange As Range, _
    ByVal HeaderTitle As String) _
As Long
    If Not HeaderRange Is Nothing Then
        If Len(HeaderTitle) > 0 Then
            Dim cIndex As Variant
            cIndex = Application.Match(HeaderTitle, HeaderRange, 0)
            If IsNumeric(cIndex) Then
                getTableColumnNumber = cIndex
            End If
        End If
    End If
End Function

请注意，列号是标题范围的第 n 列，不一定是工作表的第 n 列(例如，如果范围从列 C 开始)。
一个例子

左侧是源工作表 (Sheet1)，通常包含唯一数据(绿色列)。

右侧是目标工作表 (Sheet2)，其中黄色列最初为空白。它说明了 VLookup 的情况。无法工作，因为返回值(黄色列)位于查找值(绿色列)的左侧。

这是所有列都由其标题定义的情况。

调整常量部分中的值。

s - 来源，d - 目的地，l - 查找，m - 匹配。

Option Explicit

Sub matchValues()

    Const sName As String = "Sheet1"
    Const slHeader As String = "ID"
    Const smHeader As String = "Value"
    Const sCols As String = "A:Z"
    Const sFirst As Long = 1
    
    Const dName As String = "Sheet2"
    Const dlHeader As String = "ID"
    Const dmHeader As String = "Value"
    Const dCols As String = "A:Z"
    Const dFirst As String = 1

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim slrg As Range, smrg As Range
    With wb.Worksheets(sName)
        Dim shrg As Range: Set shrg = .Columns(sCols).Rows(sFirst)
        Dim slCol As Long: slCol = getTableColumnNumber(shrg, slHeader)
        If slCol = 0 Then Exit Sub
        Dim smCol As Long: smCol = getTableColumnNumber(shrg, smHeader)
        If smCol = 0 Then Exit Sub
        Dim srg As Range: Set srg = getDataRange(shrg)
        Set slrg = srg.Columns(slCol)
        Set smrg = srg.Columns(smCol)
    End With
    
    Dim drg As Range
    With wb.Worksheets(dName)
        Dim dhrg As Range: Set dhrg = .Columns(dCols).Rows(dFirst)
        Dim dlCol As Long: dlCol = getTableColumnNumber(dhrg, dlHeader)
        If dlCol = 0 Then Exit Sub
        Dim dmCol As Long: dmCol = getTableColumnNumber(dhrg, dmHeader)
        If dmCol = 0 Then Exit Sub
        Set drg = getDataRange(dhrg)
    End With
    
    Dim dCell As Range
    Dim cIndex As Variant
    For Each dCell In drg.Columns(dlCol).Cells
        cIndex = Application.Match(dCell.Value, slrg, 0)
        If IsNumeric(cIndex) Then
            dCell.EntireRow.Columns(dmCol).Value = smrg.Cells(cIndex).Value
        Else
            dCell.EntireRow.Columns(dmCol).Value = "Not Found"
        End If
    Next dCell

End Sub

Function getTableColumnNumber( _
    ByVal HeaderRange As Range, _
    ByVal HeaderTitle As String) _
As Long
    If Not HeaderRange Is Nothing Then
        If Len(HeaderTitle) > 0 Then
            Dim cIndex As Variant
            cIndex = Application.Match(HeaderTitle, HeaderRange, 0)
            If IsNumeric(cIndex) Then
                getTableColumnNumber = cIndex
            End If
        End If
    End If
End Function

Function getDataRange( _
    ByVal HeaderRange As Range) _
As Range
    If Not HeaderRange Is Nothing Then
        With HeaderRange.Offset(1)
            Dim lCell As Range
            Set lCell = .Resize(.Worksheet.Rows.Count - .Row + 1) _
                .Find("*", , xlFormulas, , xlByRows, xlPrevious)
            If Not lCell Is Nothing Then
                Set getDataRange = .Resize(lCell.Row - .Row + 1)
            End If
        End With
    End If
End Function