How to Do a VBA Lookup in Excel

would you like to know how do a vba lookup in excelWho doesn't know the FIND method in Excel? It is certain that many people who use Excel spreadsheets use the FIND method or use the key shortcuts Ctrl + F to find the word or content they are looking for.

But did you know that there is another way to search by ranges, cells and columns? Yes, when it says find it means that this option is focused on cells and ranges. The method to do a correct search is known as VBA in Excel.

Similarly, in the function VBA Find, you will find an option called FIND function which can help you find the value you are looking for. Now, to learn all this, we have prepared for you a tutorial to learn how to do a VBA lookup in Excel.

Here you can read about: Boom Beach Tips – Attack Strategies

Formula to find VBA function in Excel

When you are working on an Excel sheet, you normally use the key shortcut to do a search Ctrl + F to find what you are looking for. But in con to do a search VBA in Excel, You will need to write a function to find the content you are looking for. Well, let's see what the syntax of the method looks like then. FIND (search).

Even if you feel like you are lost when you see this syntax and you don't understand anything, you don't have to worry, because we will explain in depth what the syntax is about, in this example of the normal search box.

If you look closely at the key shortcut Ctrl + F is usually identical to the search syntax VBA. Now let's see what each word in the syntax means.

• What:is basically what you want to search for. Here you should mention the content you are looking for.
• After:after which cell you want to search.
• LookIn:where to look for what you want to achieve.
  • E.g.: formulas, values ​​or comments. Parameters are xlFormulas, xlValues, xlComments.
• LookAt:whether you are looking for all content or just a portion of the content. The parameters are xlWhole, xlPart.
• SearchOrder:Are you searching in rows or columns? you can use xlByRows o xlByColumns.
• SearchDirection:Are you moving to the next cell or the previous cell? xlNext, xlPrevious.
• MatchCase:the content you are searching for is case sensitive with True or False statements.
• MatchByte:This is only for double-byte languages ​​with True or False statements.
• SearchFormat:here is used to search by format. If you are looking for format, you should use the method FindFormat.

This is the syntax explanation for the VBA Find method in Excel. Apart from the first parameter, everything is optional. In the example section, we will show you how to use this find method in VBA coding.

How to use Excel VBA Lookup Function?

We will learn how to use a VBA Find Excel function with some examples.

Function to perform a VBA search in Excel: example 1

Now, we will explain a very simple example of doing a VBA search in Excel using the property FIND to find the content you are looking for. Let's assume that the data you have in your Excel sheet is below. Just follow these steps:

• Step 1: From here, you must find the name John, you open a Visual Basic to start encoding with the following parameters:
  • Code:
  • Sub Find_Ex1()
  • End Sub

• Step 2: You can't start the word here DISCOVER MOREBecause DISCOVER MORE is part of the RANGE property. So first of all, you need to mention where you are searching, i.e. the Range.

• Step 3: First you need to mention the range where you are searching. In this example, the range is B2 to B11.
  • Code:
  • Sub Find_Ex1()
  • Range ("B2:B11")
  • End Sub

• Step 4:After mentioning the range, place a period (.) And writes FIND. You should see the property FIND (Look for).

• Step 5: Select the property DISCOVER MORE and open the bracket.

• Step 6: the first argument is what you are looking for. To highlight the argument you can pass the argument as follows What: =,This would be useful to identify which parameter you are referring to.
  • Code:
  • Sub Find_Ex1()
  • Range("B2:B11").Search(What: = «John»)
  • End Sub

• Step 7:The final part is after you find the word what you want to do. You will need to select the word, so pass the argument as. Select.
  • Code:
  • Sub Find_Ex1()
  • Range("B2:B11").Find(What:="John").Select
  • End Sub

• Step 8: then run this code using the F5 key or manually as shown in the figure, so that you select the first word found Johnson containing a word,

Function to perform a VBA search in Excel: example 2

Now we will show you how to find the comment word using the search method. Here there is data and in three cells there is a comment. The cells that have a red flag have comments. From this comment, we will search for the word «No commission».

• Step 1:Start the code by mentioning the range ("D2:D11") and put a period (.) And writes Search
  • Code:
  • Sub Find_Ex2()
  • Range("D2:D11").Find(
  • End Sub

• Step 2:In the argument THAT, write the word «No commission».
  • Code:
  • Sub Find_Ex2()
  • Range("D2:D11").Search(What:="No Commission",
  • End Sub

• Step 3: ignore the part After and select the part LookIn. On LookIn, you'll be looking for this word in the comments, so select xlComments and then passes the argument as .Select
  • Code:
  • Sub Find_Ex2()
  • Range("D2:D11").Lookup(What:="No Commission", LookIn:=xlComments).Select
  • End Sub

• Step 4: Now run this code using the key F5 or manually as shown in the figure so that you select the cell that has the comment "No commission." In cell D9 you will have a comment mentioned.

Dealing with error values ​​in Excel VBA Find

If the word you are looking for is not in the range, this VBA code gives you a more accurate search that will return an error like this.

To show you that the value you are looking for is not available, you will need the following code.

If the above code found a value then it displays the value and the cell address or else it will display the message «¡The value you are looking for is not available in the provided range!

Excel VBA Find Function

When we use Search In a normal worksheet we press CTRL+F keyboard shortcut and type the data which you will need to find and if not the desired value then we move on to the next match, if there are many such matches then it is a tedious task but when we use FIND function in VBA then it does the tasks for us and gives us the exact match and it takes three arguments one is what to find, where to find and where to look.

Before moving on to VBA and starting to use the find function in macros, you should first learn what a find function is. lookup function in excel. In normal Excel, in the Home tab In the Editing group, you can find a Find function that is used to find a string or value in a range of cells or an entire worksheet.

When we click on this, we get two options;

One is simple to find

You can see that it also has an options mode that opens another feature.

Perform the search algorithm with four restrictions, Search What, Within, Search, and Find.

The second option in Excel is Find and Replace which is used when we find a string but what to replace it with any other value,

Search for function syntax

We have learned above what it is to do a VBA lookup in Excel. In VBA, you can write codes manually, but the features are the same as regular Excel. First, let's look at the syntax.

• Find (What, looking for,….)

If the value you are looking for is found with the Excel function, returns the cell where the value is, and if the value is not found, then the function object is set to nothing.

Expressions in macros are defined ranges, such as range 1 or range 2. What is a keyword for what you want to search for a specific value? Lookin is a keyword for what you are trying to search for, be it a comment, a formula, or a string.

Similarly, there are other restrictions in the Search function, which are optional. The only required field is the value you are trying to find.

Basically, VBA finds that Excel has a required argument, which is What value do you want to search for. The rest of the restrictions are optional and there are many prohibitions in the find function. The FIND function is similar to the find function in Excel.

Examples for doing a VBA search in Excel

The parameter for the lookup function is the range of cells, such as in which range you want to find a value. This can be a few columns or a few cells or an entire worksheet. Let's look at these examples:

Example 1

Suppose your data has the following values

Here we will try to find “Aran” in the same data.

• Step 1:: To write a VBA code, you need to have the developer tab in excel to be able to write VBA codes.

• Step 2:: Start by writing the code as shown below:
  • Sub Sample ()
  • Dim finds as sequence
  • Dim Rng As Range
  • find = InputBox(“Enter the value you want to find”)
  • With Sheets(“Sheet1”).Range(“A:A”)

• Step 3:: The sample is the name of the function that is given to the sub.
• Step 4:: Find the string you want the user you want to enter to search for.
• Step 5:: Rng is the variable we will take for the range.
• Step 6:: Now we ask the user to enter the value that looks like the screenshot below:

• Step 7:: Now define the search function in the module.

• Step 8:: The function finds the value entered by the user in the given range.
• Step 9:: Now you close the function with the following arguments.

• Step 10:: Now if you run the code first, the user will be prompted for a value.

• Step 10:: Once the code completes, it returns the cell where the data was found.

Example 2

In the above example, there were four unique names, but what if there were more than one name in the data, such as, consider the data below?

image

You can see that the name Aran is repeated twice in the above data. If Excel has to find the name Aran, it will find it in cell A2 and stop, but there is another value similar to A2 in cell A6. How to retrieve that value? Here comes the syntax for Find (What, After) in the help.

After defining the cell, after which reference, you want to search the data. Write the code for the above data.

• Step 1:: Always remember to enable the developer tab from the options and then from the Customizing the Ribbon in Excel to be able to write the code in VBA.
• Step 2:: In VBA, you will get objects from Microsoft Excel, which is a module where you write the codes.

• Step 3:: Previously you were working on sheet 1; now, you are working on sheet 2, so select sheet 2 for another module and a blank page will appear.

• Step 4:: Now start writing the code by defining the function first as SUB Sample2 () and press ENTER.

NOTE: : Now you will have defined the function; you will start to get into the main part, which is the definition of our variables.

• Step 5:: Defines what the Find variable should have,

• Step 6:: Select the sheets you are working on, which is sheet 2 in this example:

• Step 7:: Now you will find the text that the user enters after the cell A2, so you need to define your search function as shown below:

• Step 8:: Now close the code by ending the conditions with and if.

What the above code does is search for the string after cell A2 and return the cell wherever it is located.

You may also like: How to Improve Your Excel Experience – 13 Useful Tips

As you will see, these are the ways to do a VBA search in Excel. Each example can be useful to you in the future so that in each practice you will perfect the use of this function in your Excel projects. We hope we have helped you.