Блог

У меня есть макрос, который выполняет vlookup, беря имя поставщика в столбце J и ищет номер поставщика в массиве таблиц моего vlookup в столбцах C и D. Однако, когда я запускаю макрос, что-то явно не так с моим vlookup. Пожалуйста, смотрите формулу внутри прикрепленной картинки. По-видимому, часть моего табличного массива в моем vlookup не работает должным образом. На самом деле, я хотел бы, чтобы мой vlookup возвращал мне фиксированный табличный массив (я имею в виду с абсолютной ссылкой и долларом) из исходной точки C5 и в качестве предельной точки последнюю строку в столбце D (я имею в виду, что пределом моего табличного массива должна быть последняя строка столбца D).

Пожалуйста, посмотрите мой код VBA ниже, кажется, что эта часть моего кода VBA внутри моего vlookup неверна: C4″ amp; lastRow amp; »

Большое спасибо за вашу помощь. Xavi

 Sub insertvlookuptogetmyvendornumber()
Dim LastRow As Integer
LastRow = Range("D" amp; Rows.Count).End(xlUp).Row
PenultimateLastRow = Range("J" amp; Rows.Count).End(xlUp).Offset(-1, 0).Row 

Range("I4").Select
ActiveCell.FormulaR1C1 = "Vendor number"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],R5C3:C4" amp; LastRow amp; ",2,0)"
Selection.AutoFill Destination:=Range("I5:I" amp; PenultimateLastRow), Type:=xlFillDefault
End Sub
  

Согласно моему комментарию, я бы сохранил историческую таблицу имен и чисел. Я бы сначала прочитал это в словаре, а затем перебрал соответствующие столбцы сводной таблицы, обновив значение словаря, если имя существует. Если имя не существует, добавьте имя и номер в словарь. В конце запишите все это обратно из исторической таблицы.

Историческая таблица — это ваша текущая таблица, в которой вы пытаетесь выполнить VLookup. В этом случае эта таблица будет содержать только совпадающие пары, в которые добавлены новые значения из сводной таблицы или обновлены существующие значения.

Для повторной итерации в вашей таблице справа столбцы I и J должны содержать только совпадающие пары для начала. Жестко закодировано.

Это предполагает отсутствие промежуточных / итоговых строк в теле сводной таблицы, хотя они могут быть исключены, если присутствуют, с обновлением кода.

 Option Explicit

Public Sub UpdateReferenceTable()

    Dim lastRow As Long, dict As Object, ws As Worksheet, pvt As PivotTable, i As Long
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set pvt = ws.PivotTables("PivotTable1")

    Set dict = CreateObject("Scripting.Dictionary")

    With ws
        lastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
    End With

    Dim initialDictData(), pivotTableUpdates()
    initialDictData = ws.Range("I9:J" amp; lastRow).Value

    For i = LBound(initialDictData, 1) To UBound(initialDictData, 1)
        dict(initialDictData(i, 2)) = initialDictData(i, 1)
    Next

    Dim names(), vendorNumbers()
    names = Application.Transpose(pvt.PivotFields("Name 1").DataRange.Value)
    vendorNumbers = Application.Transpose(pvt.PivotFields("Vendor Number").DataRange.Value)

    For i = LBound(names) To UBound(names)
        If names(i) <> vbNullString Then
            If dict.exists(names(i)) Then
                dict(names(i)) = vendorNumbers(i)
            Else
                dict.Add names(i), vendorNumbers(i)
            End If
        End If
    Next
    ws.Range("I9").Resize(dict.Count, 1) = Application.Transpose(dict.items)
    ws.Range("J9").Resize(dict.Count, 1) = Application.Transpose(dict.Keys)
End Sub
  

Данные: