Vlookup е функция на работен лист в Excel, но може да се използва и в VBA, функционалността на Vlookup е подобна на функционалността в VBA и в работния лист, тъй като това е функция на работен лист, методът за използване на Vlookup във VBA е чрез Application .WorksheetFunction метод и аргументите остават същите.
Функция VLOOKUP в Excel VBA
Функцията VLOOKUP в Excel се използва за търсене на стойност в масив и връщане на съответната стойност от друга колона. Стойността за търсене трябва да присъства в първата колона. Също така се изисква да се спомене дали да се търси точно съвпадение или приблизително съвпадение. Функцията VLOOKUP на работния лист може да се използва при VBA кодиране. Функцията не е вградена VBA и по този начин може да се обажда само с помощта на работния лист.
Функцията VLOOKUP в Excel има следния синтаксис:
В която lookup_value е стойността, която трябва да се търси, table_arrray е таблицата, col_index_num е номерът на колоната на върнатата стойност, range_lookup означава дали съвпадението е точно или приблизително. range_lookup може да бъде TRUE / FALSE или 0/1.
Във VBA кода функцията VLOOKUP може да се използва като:
Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Как да използвам VLookup в Excel VBA?
По-долу са дадени някои примери за кода VLookup в Excel VBA.
Код за VLookup в Excel VBA Пример # 1
Нека видим как можем да извикаме функцията на работния лист VLOOKUP в Excel VBA.
Да предположим, че той дава данни за идентификацията, името и средните оценки, получени от тях.
Сега искате да потърсите оценките, получени от студент с ID 11004.
За да търсите стойността, изпълнете следните стъпки:
- Отидете в раздела за програмисти и кликнете върху Visual Basic.
- Под прозореца на VBA отидете на Insert и кликнете върху Module.
- Сега напишете VBA VLOOKUP кода. Може да се използва следният VBA VLOOKUP код.
Sub vlookup1 ()
Затъмняване на студент_ид като дълъг
Дим марки като дълъг
студент_id = 11004
Задайте myrange = Range (“B4: D8”)
марки = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
End Sub
Първо, дефинирайте идентификатор на студент, който е стойността за търсене. Следователно, ние определяме,
student_id = 11004
След това дефинираме диапазона, в който стойността и връщаната стойност съществуват. Тъй като данните ни присъстват в клетките B4: D8, ние дефинираме диапазон-диапазон като:
Задайте myrange = Range (“B4: D8”)
И накрая, въвеждаме функцията VLOOKUP, като използваме функцията Worksheet в променлива, маркирана като:
марки = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
За да отпечатаме маркировките в поле за съобщение, нека използваме следната команда:
MsgBox “Студент с идентификатор:” & student_id & ”получено” & марки & ”марки”
Ще се върне:
Студент с идентификационен номер: 11004 получи 85 марки.
Сега щракнете върху бутона за изпълнение.
Ще забележите, че в листа на Excel ще се появи поле за съобщение.
VLookup код в Excel VBA Пример # 2
Да предположим, че разполагате с данните за имената на служителите и тяхната заплата. Тези данни са дадени в колоните B и C. Сега трябва да напишете VBA VLOOKUP код, така че като се даде името на служителя в клетка, F4, заплатата на служителя ще бъде върната в клетката G4.
Нека напишем кода на VBA VLOOKUP.
- Определете диапазона, в който присъстват стойностите, т.е. колони Б и В.
Задайте myrange = Range (“B: C”)
- Дефинирайте името на служителя и въведете името от клетката F4.
Задайте име = Обхват (“F4”)
- Определете заплатата като клетка G4.
Задайте заплата = диапазон („G4“)
- Сега извикайте функцията VLOOKUP с помощта на WorksheetFunction във VBA и я въведете в pay.Value. Това ще върне стойността (изход на функцията Vlookup) в клетката G4. Може да се използва следният синтаксис:
pay.Value = Application.WorksheetFunction.VLookup (име, myrange, 2, False)
- Сега стартирайте модула. Клетката G4 ще съдържа заплатата на служителя, след като стартирате VBA VLOOKUP кода.
Да предположим, че промените стойността на клетка F4 на „Дейвид“ в работния лист и пуснете отново кода и той ще върне заплатата на Дейвид.
VLookup код в Excel VBA Пример # 3
Да предположим, че разполагате с данните на служителя на вашата компания, с неговия идентификационен номер, имена, отдел и заплата. Използвайки Vlookup във VBA, искате да получите данни за заплатата на служител, използвайки неговото име и отдел.
Тъй като функцията vlookup в excel търси lookup_value само в една колона, която е първата колона на масива_маси, е необходимо първо да направите колона, съдържаща „Име“ и „Отдел“ на всеки служител.
Във VBA нека вмъкнем стойностите „Име“ и „Отдел“ в колона Б на работния лист.
За да направите това, отидете в раздела за програмисти и щракнете върху Visual Basic. След това отидете на Insert и щракнете върху Module, за да стартирате нов модул.
Нека сега напишем код, така че колона B съдържа стойностите на колона D (име) и колона E.
Синтаксисът е даден като:
Първо, използвайте цикъл „for“ от i = 4, тъй като в този случай стойностите започват от 4 -ти ред. Цикълът ще продължи до края на последния ред на колона С. Така че променливата I може да се използва като номер на ред в цикъла 'for'.
След това въведете стойността, която да присвоите за Cell (number_number, колона B), която може да бъде дадена като клетки (i, „B"). Стойност, като Cell (row_number, колона D) & „_" & Cell (row_number, колона E ).
Да предположим, че искате да присвоите клетката B5 = D5 & “_” & E5, можете просто да използвате кода като:
Клетки (5, „B"). Стойност = клетки (5, „D"). Стойност & „_" & клетки (5, „E"). Стойност
Сега нека да търсим справочната стойност в масива B5: E24. Първо трябва да въведете справочната стойност. Нека вземем стойността (Име и отдел) от потребителя. Да го направя,
- дефинирайте три променливи, име, отдел и lookup_val като низ.
- Вземете въведеното име от потребителя. Използвайте кода:
name = InputBox („Въведете името на служителя“)
Съдържанието в полето за въвеждане „Въведете …“ ще се покаже в полето за подкана, когато стартирате кода. Низът, въведен в подканата, ще бъде присвоен на променливата на името.
- Вземете отдела от потребителя. Може да се направи по същия начин, както по-горе.
отдел = InputBox („Въведете отдела на служителя“)
- Задайте името и отдела с „_“ като разделител на променливата lookup_val, като използвате следния синтаксис:
lookup_val = име & “_” & отдел
- Напишете vlookup синтаксиса, за да търсите lookup_val в диапазон B5: E24 го връща в променлива заплата.
Инициализирайте променливата заплата:
Приглушена заплата толкова дълго
Използвайте функцията Vlookup, за да намерите lookup_val. Таблицата_масив може да бъде дадена като диапазон (“B: F”), а заплатата присъства в 5 -та колона. Следователно може да се използва следният синтаксис:
pay = Application.WorksheetFunction.VLookup (lookup_val, Range (“B: F”), 5, False)
- За да отпечатате заплатата в поле за съобщение, използвайте синтаксиса:
MsgBox (Заплатата на служителя е ”& заплата)
Сега стартирайте кода. В работния лист ще се появи поле за подкана, в което можете да въведете името. След като въведете името (Кажете Саши) и щракнете върху OK.
Ще се отвори друго поле, в което можете да влезете в отдела. След като влезете в отдела, кажете ИТ.
Ще отпечата заплатата на служителя.
Ако Vlookup може да намери служител с името и отдела, той ще даде грешка. Да предположим, че сте дали името „Вишну“ и отдел „ИТ“, той ще върне грешка при изпълнение „1004“.
За да разрешите този проблем, можете да посочите в кода, че при този тип грешка, вместо това отпечатайте „Стойността не е намерена“. Да го направя,
- Преди да използвате синтаксиса на vlookup, използвайте следния код-
При съобщение за грешка GoTo
Проверете:
Последващият код (на Check :) ще бъде наблюдаван и ако получи грешка, ще премине към изявлението „message“
- В края на кода (Before End Sub) посочете, че ако номерът на грешката е 1004, отпечатайте в полето за съобщение „Данните на служителите не присъстват“. Това може да стане с помощта на синтаксиса:
Съобщение:
Ако Err.Number = 1004 Тогава
MsgBox („Няма данни за служители“)
Край ако
Модул 1:
Sub vlookup3 ()За i = 4 към клетки (редове. Брой, „C"). Край (xlUp). Ред
клетки (i, „B"). Стойност = клетки (i, „D“). Стойност & „_ ”& Клетки (i,„ E ”). Стойност
Следващ iDim име As String
Dim отдел As String
Dim lookup_val Като низ
Dim заплата As Longname = InputBox („ Въведете името на служителя “)
отдел = InputBox („ Въведете отдела на служителят ”)
lookup_val = name &“ _ ”& departmentOn Error GoTo
Проверка на съобщението :
pay = Application.WorksheetFunction.VLookup (lookup_val, Range (“ B: F ”), 5, False)
MsgBox (“ Заплатата на служителя е ”& Заплата) Съобщение:
Ако Err.Number = 1004 Тогава
MsgBox („ Няма данни за служителите “)
End IfEnd Sub
Неща, които трябва да запомните за VLookup в Excel VBA
- Функцията Vlookup може да бъде извикана в Excel VBA с помощта на WorksheetFunction.
- Синтаксисът на функцията vlookup остава същият в Excel VBA.
- Когато кодът за справка на VBA не може да намери lookup_value, това ще даде грешка 1004.
- Грешката във функцията vlookup може да се управлява с помощта на оператор goto, ако връща грешка.
