ВПР в VBA Excel

Vlookup — это функция рабочего листа в Excel, но ее также можно использовать в VBA, функциональность Vlookup аналогична функциональности в VBA и на рабочем листе, поскольку это функция рабочего листа, метод использования Vlookup в VBA — через Application.WorksheetFunction метод и аргументы остаются прежними.

Функция ВПР в Excel VBA

То Функция ВПР в ExcelФункция ВПР в ExcelФункция Excel ВПР ищет определенное значение и возвращает соответствующее совпадение на основе уникального идентификатора. Уникальный идентификатор однозначно связан со всеми записями базы данных. Например, идентификатор сотрудника, номер списка учащихся, контактный номер клиента, адрес электронной почты продавца и т. д. являются уникальными идентификаторами.
читать далее
используется для поиска значения в массиве и возврата соответствующего значения из другого столбца. Значение для поиска должно присутствовать в первом столбце. Также необходимо указать, следует ли искать точное совпадение или приблизительное совпадение. Функция рабочего листа VLOOKUP может использоваться в кодировании VBA. Функция не является встроенной в VBA и поэтому может вызываться только с использованием листа.

Функция ВПР в Excel имеет следующий синтаксис:

Формула ВПР

В котором lookup_value — это значение для поиска, table_arrray — это таблица, col_index_num — это номер столбца возвращаемого значения, range_lookup указывает, является ли совпадение точным или приблизительным. range_lookup может принимать значения TRUE/FALSE или 0/1.

В коде VBA функция ВПР может использоваться как:

Application.WorksheetFunction.vlookup(lookup_value, table_array, col_index_num, range_lookup)

Как использовать VLookup в Excel VBA?

Ниже приведены некоторые примеры кода VLookup в Excel VBA.

Вы можете скачать этот шаблон VLookup в Excel VBA здесь — VLookup в шаблоне Excel VBA

Код VLookup в Excel VBA, пример №1

Давайте посмотрим, как мы можем вызвать функцию рабочего листа VLOOKUP в Excel VBA.

Предположим, у вас есть данные об удостоверениях личности учащихся, их именах и средних оценках, полученных ими.

Vlookup с Vba Пример 1

Теперь вы хотите посмотреть оценки, полученные учащимся с идентификатором 11004.

Чтобы найти значение, выполните следующие действия:

  • Перейдите на вкладку «Разработчик» и нажмите «Visual Basic».

Vlookup с Vba Пример 1-1

  • В окне VBA перейдите к «Вставить» и нажмите «Модуль».

Vlookup с Vba Пример 1-2

  • Теперь напишите код VBA VLOOKUP. Можно использовать следующий код VBA VLOOKUP.

Подпрограмма vlookup1()
Dim student_id As Long
Тусклые отметки As Long
студенческий_id = 11004
Установите мой диапазон = диапазон («B4: D8»)
отметки = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
Конец сабвуфера

Во-первых, определите идентификатор студента, который является значением для поиска. Поэтому мы определяем,

студенческий_id = 11004

Затем мы определяем диапазон, в котором существует значение и возвращаемое значение. Поскольку наши данные находятся в ячейках B4:D8, мы определяем диапазон-myrange как:

Установите мой диапазон = диапазон («B4: D8»)

Наконец, мы вводим функцию VLOOKUP, используя функцию Worksheet в переменной, помечаемой как:

отметки = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)

Чтобы напечатать метки в окне сообщения, воспользуемся следующей командой:

MsgBox «Студент с идентификатором:» & student_id & «получил» и отметки и «отметки»

Vlookup с Vba Пример 1-3

Он вернется:

Студент с ID:11004 получил 85 баллов.

Теперь нажмите кнопку запуска.

Вы заметите, что на листе Excel появится окно сообщения.

Vlookup с Vba Пример 1-4

Код VLookup в Excel VBA, пример № 2

Предположим, у вас есть данные о фамилиях сотрудников и их зарплате. Этим данным заданы столбцы B и C. Теперь вам нужно написать код VBA VLOOKUP, чтобы, учитывая имя сотрудника в ячейке F4, зарплата сотрудника была возвращена в ячейке G4.

Vlookup с Vba Пример 2

Давайте напишем код VBA VLOOKUP.

  1. Определите диапазон, в котором присутствуют значения, т. е. столбцы B и C.

Установите myrange = Range («B:C»)

  1. Определите имя сотрудника и введите имя из ячейки F4.

Имя набора = диапазон («F4»)

  1. Определите зарплату как ячейку G4.

Установить заработную плату = диапазон («G4»)

  1. Теперь вызовите функцию VLOOKUP, используя WorksheetFunction в VBA, и введите ее в зарплату.Значение. Это вернет значение (вывод функции Vlookup) в ячейке G4. Можно использовать следующий синтаксис:

зарплата.Значение = Application.WorksheetFunction.VLookup(name, myrange, 2, False)

Vlookup с Vba Пример 2-1

  1. Теперь запустите модуль. Ячейка G4 будет содержать зарплату сотрудника после запуска кода VBA VLOOKUP.

Vlookup с Vba Пример 2-2

Предположим, вы изменили значение ячейки F4 на «Дэвид» на листе и повторно запустили код, и он вернет зарплату Дэвида.

Vlookup с Vba Пример 2-3

Код VLookup в Excel VBA, пример №3

Предположим, у вас есть данные о сотруднике вашей компании с указанием его идентификатора, имени, отдела и зарплаты. Используя Vlookup в VBA, вы хотите получить информацию о зарплате сотрудника, используя его имя и отдел.

Vlookup с Vba Пример 3

Поскольку функция vlookup в Excel ищет lookup_value только в одном столбце, который является первым столбцом table_array, необходимо сначала создать столбец, содержащий «Имя» и «Отдел» каждого сотрудника.

В VBA давайте вставим значения «Имя» и «Отдел» в столбец B рабочего листа.

Для этого перейдите в Вкладка «Разработчик»Вкладка «Разработчик»Включение вкладки разработчика в Excel может помочь пользователю выполнять различные функции для VBA, макросов и надстроек, таких как импорт и экспорт XML, разработка форм и т. д. Эта вкладка по умолчанию отключена в Excel; таким образом, пользователю необходимо сначала включить его в меню параметров.читать далее и щелкните Visual Basic. Затем перейдите в «Вставить» и нажмите «Модуль», чтобы запустить новый модуль.

Давайте теперь напишем код, чтобы столбец B содержал значения столбца D (имя) и столбца E.

Синтаксис дается как:

Vlookup с Vba Пример 3-1

Во-первых, используйте цикл for из i = 4, так как значения начинаются с 4й ряд в этом случае. Цикл будет продолжаться до конца последней строки столбца C. Таким образом, переменная I может использоваться как номер строки внутри цикла for.

Затем введите значение для присвоения ячейке (номер_строки, столбец B), которое может быть указано как ячейки (i, «B»). Значение, как ячейка (номер_строки, столбец D) и «_» и ячейка (номер_строки, столбец E). ).

Предположим, вы хотите присвоить ячейке B5 = D5 & «_» & E5, вы можете просто использовать код как:

Ячейки (5, «B»). Значение = Ячейки (5, «D»). Значение и «_» и Ячейки (5, «E»). Значение

Vlookup с Vba Пример 3-2

Теперь поищем искомое значение в массиве B5:E24. Вам нужно сначала ввести значение поиска. Возьмем значение (имя и отдел) от пользователя. Сделать это,

  1. определить три переменные, имя, отдел и lookup_val в виде строки.
  2. Возьмите ввод имени от пользователя. Используйте код:

name = InputBox («Введите имя сотрудника»)

Содержимое поля ввода «Введите ..» будет отображаться в поле подсказки при запуске кода. Строка, введенная в приглашении, будет присвоена переменной имени.

  1. Возьмите отдел у пользователя. Можно сделать так же, как описано выше.

отдел = InputBox («Введите отдел сотрудника»)

  1. Назначьте имя и отдел с «_» в качестве разделителя переменной lookup_val, используя следующий синтаксис:

lookup_val = имя и «_» и отдел

Vlookup с Vba Пример 3-3

  1. Напишите синтаксис vlookup для поиска lookup_val в диапазоне B5:E24, который возвращает его в переменной зарплаты.

Инициализируйте переменную зарплату:

Тусклая зарплата

Используйте функцию Vlookup, чтобы найти lookup_val. Массив table_array может быть задан как Range(«B:F»), а зарплата присутствует в 5й столбец. Таким образом, можно использовать следующий синтаксис:

зарплата = Application.WorksheetFunction.VLookup (lookup_val, Range («B: F»), 5, False)

  1. Чтобы напечатать зарплату в окне сообщения, используйте синтаксис:

MsgBox (Зарплата работника ” & оклад)

Vlookup с Vba Пример 3-4

Теперь запустите код. На рабочем листе появится окно подсказки, в котором вы можете ввести имя. После того, как вы введете имя (Say Sashi) и нажмите OK.

Vlookup с Vba Пример 3-5

Откроется еще одно окно, в котором вы сможете войти в отдел. После того, как вы войдете в отдел, скажите IT.

Пример 3-6

Он будет печатать зарплату сотрудника.

Пример 3-7

Если Vlookup сможет найти любого сотрудника с именем и отделом, он выдаст ошибку. Предположим, вы укажете имя «Вишну» и отдел «ИТ», он вернет ошибку времени выполнения «1004».

Пример 3-8

Чтобы решить эту проблему, вы можете указать в коде, что для этого типа ошибки вместо этого печатается «Значение не найдено». Сделать это,

  1. Прежде чем использовать синтаксис vlookup, используйте следующий код:

Сообщение об ошибке GoTo

Проверять:

Завершающий код (Check:) будет контролироваться, и если он получит ошибку, он перейдет к оператору «сообщение».

  1. В конце кода (перед End Sub) укажите, что если номер ошибки 1004Номер ошибки 1004Ошибка VBA 1004 — это ошибка времени выполнения в VBA, также известная как ошибка, определяемая приложением или объектом. Поскольку в Excel ограниченное количество столбцов, мы получаем ошибку 1004, когда наш код дает команду выйти за пределы допустимого диапазона.читать далее, затем напечатайте в окне сообщения «Данные о сотруднике отсутствуют». Это можно сделать с помощью синтаксиса:

Сообщение:

Если Номер Ошибки = 1004 Тогда

MsgBox («Данные о сотруднике отсутствуют»)

Конец, если

Пример 3-9

Пример 3-10

Модуль 1:

Подпрограмма vlookup3()
Для i = 4 To Cells(Rows.Count, «C»).End(xlUp).Row
Ячейки (i, «B»). Значение = Ячейки (i, «D»). Значение & «_» и Ячейки (i, «E»). Значение
Следующее имя iDim в виде строки
Дим отдел как строка
Dim lookup_val как строка
Дим зарплата As Longname = InputBox(«Введите имя сотрудника»)
отдел = InputBox(«Введите отдел сотрудника»)
lookup_val = имя и «_» и сообщение об ошибке перехода к отделу
чек:
зарплата = Application.WorksheetFunction.VLookup (lookup_val, Range («B: F»), 5, False)
MsgBox («Зарплата работника составляет » & ЗП) Сообщение:
Если Номер Ошибки = 1004 Тогда
MsgBox («Данные о сотруднике отсутствуют»)
End IfEnd Sub

Что нужно помнить о VLookup в Excel VBA

  • Функция Vlookup может быть вызвана в Excel VBA с помощью WorksheetFunction.
  • Синтаксис функции vlookup остается прежним в Excel VBA.
  • Когда код VBA vlookup не может найти lookup_value, он выдает ошибку 1004.
  • Ошибкой в ​​функции vlookup можно управлять с помощью инструкции goto, если она возвращает ошибку.

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.