Remkomplekty.ru

IT Новости из мира ПК
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vlookup vba excel

Усовершенствованный VLOOKUP на VBA

Добрый день. Пробовал использовать функцию VLOOKUP, но она возвращает только последнее совпадение в списке.
А мне надо, так одинаковых совпадений множество, чтобы оно возвращало значение всегда из соответствующего рядка.

На второй картинке в 4 столбце значения, которые ищу, на первой картинке таблица, где осуществляется поиск и в 8 столбце значения которые должны возвращаться всегда из соответсвующего рядка.

Есть какие-то идеи?

Vlookup (ВПР) на VBA
Здавствуйте, Написал аналог функции vlookup (ВПР) на VBA. Основная задача была сделать нечто, что.

Есть ли в VBA оператор, который работает как VLOOKUP ну или Index и Match?
Всем здрасти! Простенький вопросик, есть ли в VBA оператор, который работает как VLOOKUP ну или.

Vlookup — 2,3,4 и т.д. найденнные значения
Доброе утро, помогите с написанием. и/или с идей как осуществить следующее: есть лист1, на.

VLookup с параметрами через переменные из значений функции
Не могу никак заставить работать VLookup с данными указанными в параметрах функции. Function.

Есть такая ВПР() на VBA — ищите в сети ВПР2()

Добавлено через 1 минуту
Или вот вариант:

Может даже такое и нашли бы.

Добавлено через 4 минуты
Да, параметр N — это какое по порядку нужно находить. Тут нужно по факту/месту придумать как это число подставлять, можно например использовать СЧЕТЕСЛИ().

Нашел и попробовал уже. Спасибо.
Но или я чего-то не понимаю, или это не совсем то.
Так как тут 4-м аргументом задается номер вхождения, то есть я могу написать 1,2,3,или 4, а мне надо чтобы оно возвращало правильные значения для всех вхождений автоматически.

Добавлено через 2 минуты
понял. спасибо!

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

не знаете, есть ли еще какой-то способ реализовать это сугубо через код?

У меня кстати имеется вот такой код, но работает он не совсем правильно, а так как показано на картинках, что я прикрепил ранее. Может его можно как-то поменять, чтобы он работал так как мне надо?

Добрый вечер. Прикладываю файл с тем макросом что имеется. Тот макрос, который нужно изменить называется MultipleSearch. Я в нем закомментировал весь блок который для вас не важен, чтобы он вас не сбивал с толку.

Во всем столбце J на листе Accounting output результаты от этого макроса, кроме J41:J43. Эти ячейки я отформатировал и задал туда правильный результат.

Макро берет номер счета с листа Accounting output, столбец D, сверяет его с листом KBSWG столбец A, и если они совпадают возвращает значение с листа KSWG столбец H.

Правда файл пришлось сильно почистить так как можно заливать только до 100кб, и макро отправляю отдельно, так как файлы с макросами тоже нельзя добавлять.

Буду очень признателен за помощь. Спасибо!

VLOOKUP In VBA – With Examples

In my earlier post, I had written about VLookUp in Excel. It was a massive post of around 2500 words, it explains most of the things about vertical look up function in excel. Today’s post is an extension to that post and here we will understand how to apply a VLookUp in VBA.

If you haven’t read that post then I would strongly recommend you to read that post before going any further. [Read Here]

Assuming that you have basic knowledge of VLOOKUP function we will move further.

Note: To perform these programs yourself, you may need to enable macros in excel. Read this post to know how to do this.

Syntax of VBA VLOOKUP:

You can use VLookUp in macros by following any of the below ways:

1. Application.VLOOKUP(lookup_value, table_array, column_index, range_lookup)

2. Application.WorksheetFunction.VLOOKUP(lookup_value, table_array, column_index, range_lookup)

Note: If you are searching for something similar to VLookUp function for Access then probably you should use DLOOKUP.

5 Examples of Using VLOOKUP in VBA:

Now let’s move to some practical examples of using VLookUp in VBA codes.

Example 1:Using VLookUp find the monthly salary of “Justin Jones” from the below table. Display the salary using a dialog box.

Below is the code for this:

Explanation : In this code we have used a variable ‘ E_name ’ to store the employee name whose salary is to be fetched. After this we have simply supplied the employee name and other required arguments to the VLOOKUP and it returns the salary of the corresponding Employee.

Example 2: Now make the above program a little customisable by accepting the Employee name from user. If user enters any Employee name that is not present in the table then the program should be able to convey this clearly to the user.

To accomplish this we can use the below code:

Explanation : In this code we are accepting the user input using an InputBox function. If the Employee name entered by the user is found, then VLookUp returns its corresponding salary. However if the employee name is not present in the table then VLOOKUP throws a “1004 Error”.

And, we have created an error handler to catch such cases for conveying the user that entered employee name doesn’t exist.

Example 3:In this example we will try to write a code that adds the Department field from the Employee Table 1 to our old Employee Table.

As you can see that in both these tables there is only one common column i.e. Employee_ID . So, in this case we will have to apply the VLookUp based on the Employee ID.

Below is the code to do this:

Explanation : This code takes each ‘ lookup_value ’ from the Employee ID field (one at a time), looks up its corresponding Department and then populates the corresponding department value at appropriate place.

Please note that in this code we have just pasted the result of VLookUp formula, and not the VLookUp formula itself (Refer Example 5).

Example 4:In this example we will try to write a code that displays all the details of an Employee from the Employee table (as shown below) when its Employee ID is entered.

Below is the code that can accomplish this:

Explanation :In this example we have asked user to enter the Employee Id and then we have used multiple VLookUp Statements and concatenated their outputs to show all the details in a single message box.

Читать еще:  Использование ссылок в excel

Example 5:Redo the example 3 but this time paste the whole VLookUp formula instead of pasting only the result.

Below is the code for doing this:

Explanation :This code is very similar with the one that we have discussed in Example 3, the only difference between these formulas is that here we are copying the VLookUp formula directly in the cells.

In this code we have applied the VLOOKUP in R1C1 form. So, the formula =VLOOKUP(RC[-4], R3C8:R13C9, 2, False) means =VLOOKUP( , , , ).

One thing that is worth to note here is : the square brackets ( [ ] ) in your R1C1 formula indicate that you are specifying a relative range. If you want to specify an absolute range, you need to specify the R1C1 cells without brackets; e.g. R3C8:R13C9.

So, this was all about VBA VLookUp.

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into ‘Excel Geeks’.

VLookup in VBA Excel

Vlookup is a worksheet function in excel but it can also be used in VBA, the functionality of Vlookup is similar to the functionality in VBA and in worksheet both, as it is a worksheet function the method to use Vlookup in VBA is through Application.WorksheetFunction method and the arguments remain the same.

VLookup in Excel VBA (Table of Contents)

VLOOKUP Function in Excel VBA

The VLOOKUP function in Excel is used to search a value in an array and return its corresponding value from another column. The value to search should be present in the first column. It is also required to mention whether to look for an exact match or an approximate match. The worksheet function VLOOKUP can be used in VBA coding. The function is not built in VBA and thus can only call using the worksheet.

The VLOOKUP function in Excel has the following syntax:

In which, lookup_value is the value to look for, table_arrray is the table, col_index_num is the column number of the return value, range_lookup signifies if the match is exact or approximate. range_lookup can be TRUE/FALSE or 0/1.

In VBA code, the VLOOKUP function can be used as:

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

How to Use VLookup in Excel VBA?

Below are some examples of VLookup Code in Excel VBA.

VLookup Code in Excel VBA Example #1

Let us see how we can call the worksheet function VLOOKUP in Excel VBA.

Suppose you have a data of students ID, name and the average marks obtained by them.

Now, you want to look up the marks obtained by a student, with ID 11004.

To lookup for the value, follow the following steps:

  • Go to the Developer Tab and click on Visual Basic.

  • Under the VBA window, go to Insert and click on Module.

  • Now, write the VBA VLOOKUP code. The following VBA VLOOKUP code can be used.

Sub vlookup1()
Dim student_id As Long
Dim marks As Long
student_id = 11004
Set myrange = Range(“B4:D8”)
marks = Application.WorksheetFunction.VLookup(student_id, myrange, 3, False)
End Sub

Firstly, define a student id, which is the value to lookup. Therefore, we define,

student_id = 11004

Next, we define the range in which the value and the return value exist. Since our data is present in the cells B4:D8, we define a range- myrange as:

Set myrange = Range(“B4:D8”)

Finally, we input the VLOOKUP function using the Worksheet function in a variable, marks as:

marks = Application.WorksheetFunction.VLookup(student_id, myrange, 3, False)

In order to print the marks in a message box, let us use the following command:

MsgBox “Student with ID:” & student_id & ” obtained ” & marks & ” marks”

Student with ID:11004 obtained 85 marks.

Now, click the run button.

You will notice that a message box will appear in the Excel sheet.

VLookup Code in Excel VBA Example #2

Suppose you have the data of names of employees and their salary. This data is given the columns B and C. Now, you need to write a VBA VLOOKUP code such that given a name of the employee in a cell, F4, the salary of the employee will be returned in the cell G4.

Let us write the VBA VLOOKUP code.

  1. Define the range in which the values are present, i.e., column B and C.

Set myrange = Range (“B:C”)

  1. Define the name of the employee and input the name from the cell F4.

Set name = Range (“F4”)

  1. Define salary as cell G4.

Set salary = Range (“G4”)

  1. Now, call the VLOOKUP function using WorksheetFunction in VBA and input it in salary.Value. This will return the value (output of the Vlookup function) in the cell G4. The following syntax can be used:

salary.Value = Application.WorksheetFunction.VLookup(name, myrange, 2, False)

  1. Now, run the module. The cell G4 will contain the salary of the employee after you run the VBA VLOOKUP code.

Suppose you change the value of cell F4 to “David” in the worksheet and re-run the code, it will return the salary of David.

VLookup Code in Excel VBA Example #3

Suppose you have the data of the employee of your company, having their ID, names, Department, and salary. Using Vlookup in VBA, you want to get the salary details of an employee using his name and department.

Since the vlookup function in excel searches the lookup_value in only a single column, which is the first column of the table_array, it is required that you first make a column containing the “Name” and “Department” of each employee.

In VBA, let us insert the values “Name” and “Department” in column B of the worksheet.

To do this, go to the Developer tab and click Visual Basic. Then go to insert and click on Module to start a new module.

Let us now write code, such that column B contains the values of column D (name) and column E.

The syntax is given as:

Читать еще:  Повернуть видео бесплатно

Firstly, use a ‘for’ loop from i = 4 since the values are starting from the 4 th row in this case. The loop will continue until the end of the last row of column C. So, the variable i can be used as a row number inside the ‘for’ loop.

Then enter the value to assign for Cell (row_number, column B), which can be given as Cells (i, “B”).Value, as Cell (row_number, column D) & “_” & Cell (row_number, column E).

Suppose you want to assign the cell B5 = D5 & “_” & E5, you can simply use the code as:

Cells(5, “B”).Value = Cells(5, “D”).Value & “_” & Cells(5, “E”).Value

Now, let to look for the lookup value in the array B5:E24, you need to first enter the lookup value. Let us take the value (Name and Department) from the user. To do this,

  1. define three variables, name, department and lookup_val as a string.
  2. Take the name input from the user. Use the code:

name = InputBox (“Enter the name of the employee”)

The content in the input box “Enter the ..” will be displayed in the prompt box when you run the code. The string entered in the prompt will be assigned to the name variable.

  1. Take the department from the user. The can be done similarly as above.

department = InputBox (“Enter the department of the employee”)

  1. Assign the name and department with “_” as a separator to the variable lookup_val using the following syntax:

lookup_val = name & “_” & department

  1. Write the vlookup syntax to search for the lookup_val in range B5:E24 return it in a variable salary.

Initialize the variable salary:

Dim salary As Long

Use the Vlookup function to find the lookup_val. The table_array can be given as Range(“B:F”) and the salary is present in the 5 th column. The following syntax can thus be used:

salary = Application.WorksheetFunction.VLookup(lookup_val, Range (“B:F”), 5, False)

  1. To print the salary in a message box, use the syntax:

MsgBox (The salary of the employee is ” & salary)

Now, run the code. A prompt box will appear in the worksheet in which you can enter the name. After you enter the name (Say Sashi) and click OK.

It will open another box in which you can enter the department. After you enter the department, say IT.

It will print the salary of the employee.

If the Vlookup can find any employee with the name and department, it will give an error. Suppose you give the name “Vishnu” and department “IT”, it will return Run-time error ‘1004’.

To address this issue, you can specify in the code, that on this type of error, print “Value not found” instead. To do this,

  1. Before using the vlookup syntax, use the following code-

On Error GoTo Message

Check:

The trailing code (of Check:) will be monitored, and if it receives any error, it will go to the “message” statement

  1. At the end of the code (Before End Sub), specify that if the error number is 1004, then print in the message box “Employee data not present”. This can be done using the syntax:

Message:

If Err.Number = 1004 Then

MsgBox (“Employee data not present”)

End If

Module 1:

Things to Remember About VLookup in Excel VBA

  • The Vlookup function can be called in Excel VBA by using WorksheetFunction.
  • The syntax of vlookup function remains the same in Excel VBA.
  • When VBA vlookup code cannot find the lookup_value, it will give 1004 error.
  • The error in the vlookup function can be managed using a goto statement if it returns an error.

Recommended Articles

This has been a guide to VLookup in VBA. Here we discuss how to write VLookup Code in Excel VBA along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

RAD Excel

VLOOKUP is exposed in VBA via the WorksheetFunction class and works much the same as it does when you use it in a worksheet, but one thing you have to be careful of is when you have a date as the lookup_value . Let’s use the data from a previous blog post to demonstrate the problem:

The formula in cell I2 clearly works without issue, but when we replicate it in VBA we may get this error:

Run-time error ‘1004’: Unable to get the VLookup property of the WorksheetFunction class.

This error means that it can’t find the lookup_value in the lookup column (equivalent of #N/A) and, in this case, we will get this error whether we take the lookup_value date directly from the H2 cell using Range.Value or if we use a Date variable. You can try running the two procedures below to see this for yourself.

To understand why there is a problem we have to put on both our Excel and VBA hats. In the previous post, Why Does VLOOKUP Return An #N/A Error?, we saw that VLOOKUP is data type sensitive: for example, VLOOKUP considers the string “3” to be different to the number 3. The post also mentioned that Excel worksheets can only contain five data types: numbers (also known as doubles), text (also known as strings), logicals (also known as booleans), error values and arrays. You’ll notice that Excel does not have a Date data type: in Excel, dates and times are numbers which have just been formatted to look like dates. In VBA, however, there is a Date data type and, in this case, when we use the Range.Value property to retrieve the value from the cell H2, it returns a Date type. VLOOKUP is unable to match this Date type to the numbers (formatted as dates) the the lookup column, which is why we get the error.

There are a number of workarounds to the issue. Essentially we have to make sure that the lookup_value data type matches the data types in the lookup column. Since there’s only one lookup_value it makes sense that we concentrate on changing its type rather than anything else.

If you are reading the date lookup_value from a cell then you can either just pass the range reference into VLOOKUP (exclude the .Value ), or you can use the Range.Value2 property instead of the Range.Value property. The Range.Value2 property doesn’t use the Date data type so, by using Range.Value2 instead, we get a Double data type holding the value 40911. VLOOKUP will be able to process that quite happily. Another option would be to change the formatting of the cell H2 to that which represents a number but it would not be a very robust solution. Here’s the first error example re-written so that it uses the Range.Value2 property:

Читать еще:  Excel column width

Yet another option, which will work whether you are picking up the date from a cell or using a Date variable, is to explicitly convert the Date into a Double or Long data type using the CDbl or CLng functions. Note that Long data types would apply for whole dates only. Here’s the second error example re-written with the CDbl function included:

VBA VLOOKUP :
How to Use Worksheet Functions in VBA

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Visual Basic for Applications (VBA) has many built-in functions that help you work with spreadsheet data.

But Excel is the undisputed king when it comes to useful functions.

Fortunately, you can use worksheet functions to take advantage of that power when you’re writing macros in VBA.

First, you learn the differences between VBA functions and worksheet functions.

Then I show you how to use a worksheet function in VBA – the VLOOKUP!

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Table of Contents

Get your FREE exercise file

In the second section of this post, we’ll be going through an example.

Download the free example workbook so you can follow along!

BONUS: Download the VBA VLOOKUP Exercise File to go along with this post.

VBA functions vs. worksheet functions

VBA functions are built into Visual Basic for Applications, the scripting language that you use to create macros.

You can use VBA functions in any program that supports VBA (including Microsoft Word and Access).

Worksheet functions are specific to Excel. They’re the functions that you’re used to using in spreadsheets already—things like CONCATENATE, COMBIN, and VLOOKUP.

You could get the same information from VBA without using worksheet functions—but in many cases, you’d have to write a lot of code that’s already been worked out in Excel.

Using worksheet functions saves you time over writing your own functions in VBA.

To call a worksheet function, you’ll need to use the following syntax:

Let’s walk through an example of a very useful function that isn’t present in VBA: VLOOKUP.

Using VLOOKUP in VBA

We’ll use a simple VLOOKUP example to see how worksheet functions can be called in VBA.

VLOOKUP is a very powerful Excel function that’s great for finding information in big spreadsheets—but unfortunately, it’s not built into VBA. So we’ll have to call it with a worksheet function.

If you’re not familiar with the function, check out our full guide to VLOOKUP, which will walk you through it in detail.

Open the example workbook to follow along. It contains a list of product numbers and descriptions. We’ll use VBA to look up descriptions based on product numbers.

Here’s the VBA script we’ll use:

The first two lines of the script are simple; they declare prodNum as an integer variable and prodDesc as a String variable, then assign the value in cell F2 to prodNum.

F2 is the cell where we’ll ask users to input a product number.

The line that calls VLOOKUP is actually quite simple. To use an Excel function, type “Application.WorksheetFunction.” and start typing the name of the function.

You’ll see it come up in the resulting list (you can also just type the name of the function you’re looking for).

Then you’ll need to include the standard arguments for the function. In VLOOKUP, those are lookup_number, table_array, col_index_num, and range_lookup.

You enter them similarly to how you would in Excel, but there are a few differences. In our case, lookup_number is the variable prodNum, which is similar to selecting a cell in Excel.

The table_array, however, needs to be presented in a format that VBA can handle. Here we’ve used Range(“A1:B51”), which selects the cells in A1:B51. It’s important to remember that you can’t just type “A1:B51”, as VBA won’t recognize the range.

col_index_num and range_lookup are the same as in Excel. We’re looking in the second column and want an exact match for the product number, so we’ll use 2 and FALSE.

Make sure that there’s a product number in F2:

Then run the macro:

VBA displays a message box with the matching product description for our product number.

This would be a great place to link a button to run a macro! Users would just have to enter the product number and click a button.

Why use worksheet functions in VBA?

This is more complicated than just using the function in Excel, so why would you write a VBA macro for a VLOOKUP?

Any function that’s present in Excel is going to be slightly more complicated to use in VBA. But having access to them gives you the option of very powerful automation.

For example, if you wanted to run multiple VLOOKUPs and have the results put in a table, you might find that you need to manually create each VLOOKUP in Excel.

With VBA, you can write a script that automates the process and saves you time. Even if it is possible using functions in Excel, it will likely be much more efficient in VBA. You can also save the script and run it on other spreadsheets.

Remember that you can use almost any Excel function from within VBA. Just type “Application.WorksheetFunction.” and you’ll see options in the VBA window.

You can also call these functions with Application.[function name]. There are advantages to both, but they get into some complex programming concepts.

When you’re getting started, use the full line with “WorksheetFunction.”

Ссылка на основную публикацию
Adblock
detector