Блог

Главная - Вопросы по программированию - VLookup VBA по имени столбца

VLookup VBA по имени столбца

#excel #vba

Вопрос:

Я пытаюсь выполнить VLookup, в котором столбец данных, которые я буду извлекать, может отличаться. Как я могу искать имя столбца вместо того, чтобы указывать фиксированный номер столбца?

Application.VLookup(.Cells(row, 1).value, Worksheets("Sheet1").Range("A2:Z" amp; lastRow_Sheet1), 4, 0)

Application.VLookup(.Cells(row, 1).value, Worksheets("Sheet1").Range("A2:Z" amp; lastRow_Sheet1), COLUMNNAME, 0)

Я пытаюсь заставить функцию СОПОСТАВЛЕНИЯ работать здесь, но мне не везет:

Application.VLookup(.Cells(row, 1).value, Worksheets("Sheet1").Range("A2:Z" amp; lastRow_Sheet1), WorksheetFunction.Match("Column Name", "A1:Z1", 0), 0)

Изменить: Решение здесь

Application.VLookup(.Cells(row, 1).value, Worksheets("Sheet1").Range("A2:Z" amp; lastRow_Sheet1), WorksheetFunction.Match("Column Name", Range("A1:Z1"), 0), 0)

Комментарии:

1. Как это MATCH не работает?

2. Я получаю 1004 ошибки во время выполнения: Не удалось получить свойство Match класса WorksheetFunction. Я не понимаю, почему это так, я вижу, что имя столбца, которое я ищу, находится в указанном мной диапазоне.

3. Вы не указали диапазон в MATCH — "A1:Z1" это строка.

4. @norie Спасибо за это, с другой стороны, это работает только тогда, когда я активно нахожусь на вкладке, если я нахожусь на другой вкладке, я получаю ту же ошибку

5. Нет проблем, кстати, я бы посоветовал вам сделать MATCH это отдельно перед поиском. Это даст вам возможность избежать ошибок, если имя столбца по какой-либо причине не найдено.

Ответ №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
Метки: